pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery choosing Bad Index Path
4+ messages / 3 participants
[nested] [flat]
* Query choosing Bad Index Path
@ 2022-02-07 05:15 Valli Annamalai <aishwaryaanns@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Valli Annamalai @ 2022-02-07 05:15 UTC (permalink / raw)
To: pgsql-performance
*Postgres version:* 11.4
*Problem:*
Query choosing Bad Index Path. Details are provided below:
*Table :*
*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):
Attachments:
[image/jpeg] 1644210682131000_1988131564.jpeg (124.8K, ../../CADkhgiJ+gT_FDKZWgP8oZsy6iRbYMYkmRjsPhqhcT1A2KBgcHA@mail.gmail.com/3-1644210682131000_1988131564.jpeg)
download | view image
[image/jpeg] 2.jpg (220.1K, ../../CADkhgiJ+gT_FDKZWgP8oZsy6iRbYMYkmRjsPhqhcT1A2KBgcHA@mail.gmail.com/4-2.jpg)
download | view image
[image/jpeg] 3.jpg (313.7K, ../../CADkhgiJ+gT_FDKZWgP8oZsy6iRbYMYkmRjsPhqhcT1A2KBgcHA@mail.gmail.com/5-3.jpg)
download | view image
^ permalink raw reply [nested|flat] 4+ messages in thread
* Query choosing Bad Index Path
@ 2022-02-07 06:06 Valli Annamalai <aishwaryaanns@gmail.com>
parent: Valli Annamalai <aishwaryaanns@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Valli Annamalai @ 2022-02-07 06:06 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org
*Postgres version:* 11.4
*Problem:*
Query choosing Bad Index Path. Details are provided below:
*Table :*
*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):
Attachments:
[image/jpeg] 1644210682131000_1988131564.jpeg (124.8K, ../../CADkhgiLxN_kyWur8B9h0tuFEgSuJD4BgMTLBOQihdn-MBU7ptQ@mail.gmail.com/3-1644210682131000_1988131564.jpeg)
download | view image
[image/jpeg] 2.jpg (220.1K, ../../CADkhgiLxN_kyWur8B9h0tuFEgSuJD4BgMTLBOQihdn-MBU7ptQ@mail.gmail.com/4-2.jpg)
download | view image
[image/jpeg] 3.jpg (313.7K, ../../CADkhgiLxN_kyWur8B9h0tuFEgSuJD4BgMTLBOQihdn-MBU7ptQ@mail.gmail.com/5-3.jpg)
download | view image
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query choosing Bad Index Path
@ 2022-02-07 06:18 Pavel Stehule <pavel.stehule@gmail.com>
parent: Valli Annamalai <aishwaryaanns@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Pavel Stehule @ 2022-02-07 06:18 UTC (permalink / raw)
To: Valli Annamalai <aishwaryaanns@gmail.com>; +Cc: pgsql-performance
Hi
po 7. 2. 2022 v 6:15 odesÃlatel Valli Annamalai <aishwaryaanns@gmail.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
Attachments:
[image/jpeg] 1644210682131000_1988131564.jpeg (124.8K, ../../CAFj8pRA0h2daVGOra5RD_eG=Tgo=-fvCSN5jXwe-K8PzOvRj8A@mail.gmail.com/3-1644210682131000_1988131564.jpeg)
download | view image
[image/jpeg] 2.jpg (220.1K, ../../CAFj8pRA0h2daVGOra5RD_eG=Tgo=-fvCSN5jXwe-K8PzOvRj8A@mail.gmail.com/4-2.jpg)
download | view image
[image/jpeg] 3.jpg (313.7K, ../../CAFj8pRA0h2daVGOra5RD_eG=Tgo=-fvCSN5jXwe-K8PzOvRj8A@mail.gmail.com/5-3.jpg)
download | view image
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query choosing Bad Index Path
@ 2022-02-07 06:18 Julien Rouhaud <rjuju123@gmail.com>
parent: Valli Annamalai <aishwaryaanns@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Julien Rouhaud @ 2022-02-07 06:18 UTC (permalink / raw)
To: Valli Annamalai <aishwaryaanns@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
Hi,
On Mon, Feb 07, 2022 at 11:36:17AM +0530, Valli Annamalai wrote:
> *Postgres version:* 11.4
>
> *Problem:*
> Query choosing Bad Index Path. Details are provided below:
>
>
> *Table :*
>
>
>
>
>
>
> *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 already sent the same email less than an hour ago on pgsql-performance
([1]), which is the correct mailing list, please don't post on this mailing
list also.
Note also that sending information as images can be problematic as some people
here (including me) won't be able to see them. I tried on a web-based MUA and
that's not really better though, as the images are hardly readable and
definitely not grep-able. You will likely have more answers sending
information in plain text.
[1] https://www.postgresql.org/message-id/CADkhgiJ+gT_FDKZWgP8oZsy6iRbYMYkmRjsPhqhcT1A2KBgcHA@mail.gmail...
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-02-07 06:18 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-02-07 05:15 Query choosing Bad Index Path Valli Annamalai <aishwaryaanns@gmail.com>
2022-02-07 06:06 ` Valli Annamalai <aishwaryaanns@gmail.com>
2022-02-07 06:18 ` Julien Rouhaud <rjuju123@gmail.com>
2022-02-07 06:18 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox