I've the following query
SELECT * FROM "items" WHERE (("field1" = 'somevalue' OR "field2" = 'somevalue' OR "field3" = 'somevalue')) ORDER BY "createdAt" DESC LIMIT 20 OFFSET 0;
Here is the result of
EXPLAIN ANALYZE in staging
Limit (cost=118.72..118.77 rows=20 width=2212) (actual time=0.407..0.416 rows=20 loops=1) -> Sort (cost=118.72..118.80 rows=30 width=2212) (actual time=0.405..0.412 rows=20 loops=1) Sort Key: "createdAt" DESC Sort Method: quicksort Memory: 75kB -> Bitmap Heap Scan on items item (cost=13.10..117.98 rows=30 width=2212) (actual time=0.078..0.231 rows=25 loops=1) Recheck Cond: (("col1" = 'something'::text) OR ("col2" = 'something'::text) OR (col3 = 'something'::text)) Heap Blocks: exact=21 -> BitmapOr (cost=13.10..13.10 rows=30 width=0) (actual time=0.056..0.056 rows=0 loops=1) -> Bitmap Index Scan on "item_col1_col2_col3_index" (cost=0.00..4.47 rows=25 width=0) (actual time=0.038..0.038 rows=25 loops=1) Index Cond: ("col1" = 'something'::text) -> Bitmap Index Scan on "col2_index" (cost=0.00..4.29 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=1) Index Cond: ("col2" = 'something'::text) -> Bitmap Index Scan on col3_index (cost=0.00..4.31 rows=4 width=0) (actual time=0.008..0.008 rows=0 loops=1) Index Cond: (col3 = '5a9793161131a39df46fe851'::text) Planning time: 0.867 ms Execution time: 0.631 ms
item_col1_col2_col3_index, as the name suggests, is a compound index of
And here is the result of
EXPLAIN ANALYZE in production
Limit (cost=0.43..13020.16 rows=20 width=2378) (actual time=1060451.457..1060451.457 rows=0 loops=1) -> Index Scan Backward using "createdAt_index" on items item (cost=0.43..7711586.19 rows=11846 width=2378) (actual time=1060451.453..1060451.453 rows=0 loops=1) Filter: (("col1" = 'something'::text) OR ("col2" = 'something'::text) OR (col3 = 'something'::text)) Rows Removed by Filter: 2987649 Planning time: 0.539 ms Execution time: 1060459.032 ms
As you can see, in production it was executed with
createdAt_index. The process of filtered out unmatched records were done without index though the
item_col1_col2_col3_index exists. Therefore it took very long.
The main difference is in staging, the table only has about 5000 records. In production, it contains about 3.4 million records
I've been running
VACUUM ANALYZE items since 2 hours ago (it's still running at the time I posted this question)
Is there any idea how can I improve the speed of the query?