Re: Query choosing Bad Index Path

Hi

po 7. 2. 2022 v 6:15 odesílatel Valli Annamalai <aishwaryaanns(at)gmail(dot)com>
napsal:

>
> *Postgres version:* 11.4
>
> *Problem:*
> Query choosing Bad Index Path. Details are provided below:
>
>
> *Table :*
>
>
>
>
>
>
>
please, don't use screenshots

> *Doubt*
> 1. 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_idx7
>
> Below 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.0050666
>
> Since, 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