From: Floris Van Nee <florisvannee@Optiver.com>
To: Dmitry Dolgov <9erthalion6@gmail.com>
To: Jesper Pedersen <jesper.pedersen@redhat.com>
Cc: Rafia Sabih <rafia.pghackers@gmail.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Thomas Munro <thomas.munro@gmail.com>
Cc: Alexander Kuzmenkov <a.kuzmenkov@postgrespro.ru>
Cc: Peter Geoghegan <pg@bowt.ie>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: Bhushan Uparkar <bhushan.uparkar@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Alexander Korotkov <a.korotkov@postgrespro.ru>
Cc: James Coleman <jtc331@gmail.com>
Subject: Re: Index Skip Scan
Date: Wed, 5 Jun 2019 19:39:29 +0000
Message-ID: <1559763570329.85227@Optiver.com> (raw)
In-Reply-To: <1559763453006.85615@Optiver.com>
References: <2685f020-f302-9bf6-1b5c-ebc2bcebc635@2ndquadrant.com>
<CA+q6zcUSMQtDVr-isZkm8FP4Mh6APkNyzkPYQPr1ZugeDV2XPA@mail.gmail.com>
<CA+q6zcUSuFBhGVFZN_AVSxRbt5wr_4_YEYwv8PcQB=m6J6Zpvg@mail.gmail.com>
<20190315.125452.252656146.horiguchi.kyotaro@lab.ntt.co.jp>
<CA+q6zcXcSftAxfwuK5c186E8ckZeWmOO07GFvpoOx-wpsDGGzA@mail.gmail.com>
<CA+q6zcVn10QzbSh686PaZpxgxMhxABRj7aJJpsJ=tKkmcSrBew@mail.gmail.com>
<CA+q6zcUVAnfSOaSk3s9Sbj6yC7LrDYChSyJd0t7u6Q=97k5a=A@mail.gmail.com>
<CA+q6zcVQG0ufA_5m_7eeHLN2VRy+z8Kf9FoZ2NnF=yd0_43ndg@mail.gmail.com>
<CA+q6zcXxwU1FZ=Tj0kWeTkrJ1N9=fhrUH1t5N_yHEcKsG=+qUw@mail.gmail.com>
<1559361698401.22864@Optiver.com>
<1559362223126.18661@Optiver.com>
<CA+FpmFeKh9BAhDTAf-ZWQ=pvVaRy+Lnahb_8=OOFqcVXJDBWWQ@mail.gmail.com>
<988b7763-5c55-1be1-b7b6-f3a68eff0d40@redhat.com>
<CA+q6zcVnRRqktw4cg2Vddy3S+VY3Tsmit0kausYtctvsTum7tA@mail.gmail.com>
<1559763453006.85615@Optiver.com>
> To address this, probably we can do something like in the attached patch.
> Altogether with distinct_pathkeys uniq_distinct_pathkeys are stored, which is
> the same, but without the constants elimination. It's being used then for
> getting the real number of distinct keys, and to check the order of the columns
> to not consider index skip scan if it's different. Hope it doesn't
> look too hacky.
>
Thanks! I've verified that it works now.
I was wondering if we're not too strict in some cases now though. Consider the following queries:
postgres=# explain(analyze) select distinct on (m,f) m,f from t where m='M2';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Only Scan using t_m_f_t_idx on t (cost=0.29..11.60 rows=40 width=5) (actual time=0.056..0.469 rows=10 loops=1)
Scan mode: Skip scan
Index Cond: (m = 'M2'::text)
Heap Fetches: 10
Planning Time: 0.095 ms
Execution Time: 0.490 ms
(6 rows)
postgres=# explain(analyze) select distinct on (f) m,f from t where m='M2';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=0.29..849.83 rows=10 width=5) (actual time=0.088..10.920 rows=10 loops=1)
-> Index Only Scan using t_m_f_t_idx on t (cost=0.29..824.70 rows=10052 width=5) (actual time=0.087..8.524 rows=10000 loops=1)
Index Cond: (m = 'M2'::text)
Heap Fetches: 10000
Planning Time: 0.078 ms
Execution Time: 10.944 ms
(6 rows)
This is basically the opposite case - when distinct_pathkeys matches the filtered list of index keys, an index skip scan could be considered. Currently, the user needs to write 'distinct m,f' explicitly, even though he specifies in the WHERE-clause that 'm' can only have one value anyway. Perhaps it's fine like this, but it could be a small improvement for consistency.
-Floris?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: florisvannee@Optiver.com, 9erthalion6@gmail.com, jesper.pedersen@redhat.com, rafia.pghackers@gmail.com, jeff.janes@gmail.com, thomas.munro@gmail.com, a.kuzmenkov@postgrespro.ru, pg@bowt.ie, tomas.vondra@2ndquadrant.com, thomas.munro@enterprisedb.com, bhushan.uparkar@gmail.com, a.korotkov@postgrespro.ru, jtc331@gmail.com
Subject: Re: Index Skip Scan
In-Reply-To: <1559763570329.85227@Optiver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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