| From: | Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> |
|---|---|
| To: | Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> |
| Cc: | Ashutosh Bapat <ashutosh(dot)bapat(at)2ndquadrant(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: Make the qual cost on index Filter slightly higher than qual cost on index Cond. |
| Date: | 2020-05-26 23:12:52 |
| Message-ID: | CAKU4AWqZ+Z4FEGZavtbGx0_q18so44N=PcvsprSjDUVtcJEkkA@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
You can use the attached sql to reproduce this issue, but I'm not sure you
can
get the above result at the first time that is because when optimizer think
the
2 index scan have the same cost, it will choose the first one it found, the
order
depends on RelationGetIndexList. If so, you may try drop and create
j1_i_im5 index.
The sense behind this patch is we still use the cost based optimizer, just
when we
we find out the 2 index scans have the same cost, we prefer to use the
index which
have more qual filter on Index Cond. This is implemented by adjust the
qual cost
on index filter slightly higher.
The issue here is not so uncommon in real life. consider a log based
application, which
has serval indexes on with create_date as a leading column, when the
create_date
first load the for the given day but before the new statistics is gathered,
that probably run
into this issue.
--
Best Regards
Andy Fan
| Attachment | Content-Type | Size |
|---|---|---|
| index_choose.sql | application/octet-stream | 353 bytes |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Mark Dilger | 2020-05-26 23:19:37 | New 'pg' consolidated metacommand patch |
| Previous Message | Andy Fan | 2020-05-26 22:49:40 | Re: Make the qual cost on index Filter slightly higher than qual cost on index Cond. |