How to speed up query execution in Postgres?

Question

SQL

There is a table with integer fields A and B, and the B-tree index is built on the field A. There are about 10 million records in the table. The following query is executed:
SELECT b FROM table1 WHERE a IN ( ... 1000 values ​​... ) ORDER BY a The
query takes approximately 100 seconds to complete.
The customer complains that it takes a long time.
Does anyone have any ideas how to speed up the execution of this query?

Answer 1

There are several ways to speed up the query.
1. hardware optimization
2. database
optimization 3. query optimization
1. View hard disk load during query execution via iostat -x 1
If %util hits 100% to think about speeding up the disk subsystem, it is possible to set raid-0
2. this is a broad topic. In short, you need to allow the database to use more memory during query execution. See aside: shared_buffers, work_mem, maintenance_work_mem and postgresql
3 configuration articles. We look at the indexes themselves and how to work with them. Perhaps the indexes in the columns need to be changed from GiST to GIN ( www.postgresql.org/docs/9.1/static/textsearch-inde...Perhaps it is necessary to make indexes on several columns so that the WHERE a AND b construction works faster.
Even if the same data transformation action is performed during the query, it may be necessary to preprocess the data so that this calculation is already ready (lies in a separate column) and then build an index on it and drive the selection.
I drive queries according to the data of the world in tables of 100 million records. Complex selections are processed in less than a second.

Answer 2

Well for a start it is necessary to look at the request plan. I myself once solved a similar problem by replacing where .. in with a bunch of requests a = ... through union, but in my case there were not 1000 values, but somewhat less.