Hi
po 7. 2. 2022 v 6:15 odesílatel Valli Annamalai <aishwaryaanns@xxxxxxxxx> napsal:
Postgres version: 11.4Problem:Query choosing Bad Index Path. Details are provided below:
Table :
please, don't use screenshots
Doubt1. Why is this Query choosing Index Scan Backward using table1_pkey Index though it's cost is high. It can rather choose
BITMAP OR(Index on RECORDID) i.e; table1_idx6(Index on RELATEDID) i.e; table1_idx7Below is the selectivity details from pg_stats table- Recordid has 51969 distinct values. And selectivity (most_common_freqs) for recordid = 15842006928391817 is 0.00376667- Relatedid has 82128 distinct values. And selectivity (most_common_freqs) for recordid = 15842006928391817 is 0.0050666Since, selectivity is less, this should logically choose this Index, which would have improve my query performance here.
I cross-checked the same by removing PrimaryKey to this table and query now chooses these indexes and response is in 100ms. Please refer the plan below (after removing primary key):
You can see very bad estimation 32499 x 0 rows
Next source of problems can be LIMIT clause. Postgres expects so data are uniformly stored, and then LIMIT 10 quickly finds wanted rows. But it is not true in your case.
You can try to use a multicolumn index, or you can transform your query from OR based to UNION ALL based
SELECT * FROM tab WHERE p1 OR p1 => SELECT * FROM tab WHERE p1 UNION ALL SELECT * FROM tab WHERE p2
Regards
Pavel
