| From: | Jesper Pedersen <jesper(dot)pedersen(at)redhat(dot)com> |
|---|---|
| To: | Floris Van Nee <florisvannee(at)Optiver(dot)com> |
| Cc: | Dmitry Dolgov <9erthalion6(at)gmail(dot)com>, Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>, Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Thomas Munro <thomas(dot)munro(at)gmail(dot)com>, Alexander Kuzmenkov <a(dot)kuzmenkov(at)postgrespro(dot)ru>, Peter Geoghegan <pg(at)bowt(dot)ie>, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>, Bhushan Uparkar <bhushan(dot)uparkar(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>, James Coleman <jtc331(at)gmail(dot)com> |
| Subject: | Re: Index Skip Scan |
| Date: | 2019-06-03 18:13:56 |
| Message-ID: | b0a83318-5bae-3996-4231-9f2d6565856f@redhat.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi Floris,
On 6/1/19 12:10 AM, Floris Van Nee wrote:
> Given a table definition of (market text, feedcode text, updated_at timestamptz, value float8) and an index on (market, feedcode, updated_at desc) (note that this table slightly deviates from what I described in my previous mail) and filling it with data.
>
>
> The following query uses an index skip scan and returns just 1 row (incorrect!)
>
> select distinct on (market, feedcode) market, feedcode
> from streams.base_price
> where market='TEST'
>
> The following query still uses the regular index scan and returns many more rows (correct)
> select distinct on (market, feedcode) *
> from streams.base_price
> where market='TEST'
>
>
> It seems that partially filtering on one of the distinct columns triggers incorrect behavior where too many rows in the index are skipped.
>
>
Thanks for taking a look at the patch, and your feedback on it.
I'll def look into this once I'm back from my travels.
Best regards,
Jesper
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jesper Pedersen | 2019-06-03 18:16:45 | Re: Index Skip Scan |
| Previous Message | Elvis Pranskevichus | 2019-06-03 17:44:44 | Re: WITH NOT MATERIALIZED and DML CTEs |