From: Floris Van Nee <florisvannee@Optiver.com>
To: Dmitry Dolgov <9erthalion6@gmail.com>
Cc: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Thomas Munro <thomas.munro@gmail.com>
Cc: Jesper Pedersen <jesper.pedersen@redhat.com>
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: Sat, 1 Jun 2019 15:33:57 +0000
Message-ID: <1559403239721.74966@Optiver.com> (raw)
In-Reply-To: <CA+q6zcU=8OEKw8bc2DHsf7xSFqaT7gOjc-_nbX8FO1N4eizQHg@mail.gmail.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+q6zcU=8OEKw8bc2DHsf7xSFqaT7gOjc-_nbX8FO1N4eizQHg@mail.gmail.com>
Hi,
Thanks for the helpful replies.
> Yes, good catch, I'll investigate. Looks like in the current implementation
> something is not quite right, when we have this order of columns in an index
> and where clause (e.g. in the examples above everything seems fine if we create
> index over (feedcode, market) and not the other way around).
I did a little bit of investigation and it seems to occur because in pathkeys.c the function pathkey_is_redundant considers pathkeys redundant if there is an equality condition with a constant in the corresponding WHERE clause.
* 1. If the new pathkey's equivalence class contains a constant, and isn't
* below an outer join, then we can disregard it as a sort key. An example:
* SELECT ... WHERE x = 42 ORDER BY x, y;
In planner.c it builds the list of distinct_pathkeys, which is then used for the index skip scan to skip over the first length(distinct_pathkeys) columns when it does a skip. In my query, the distinct_pathkeys list only contains 'feedcode' and not 'market', because 'market' was considered redundant due to the WHERE clause. However, the index skip scan interprets this as that it has to skip over just the first column.
We need to get this list of number of prefix columns to skip differently while building the plan. We need the 'real' number of distinct keys without throwing away the redundant ones. However, I'm not sure if this information can still be obtained while calling create_skipscan_unique_path? But I'm sure people here will have much better ideas than me about this :-)
-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, horiguchi.kyotaro@lab.ntt.co.jp, jeff.janes@gmail.com, thomas.munro@gmail.com, jesper.pedersen@redhat.com, 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: <1559403239721.74966@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