From: Floris Van Nee <florisvannee@Optiver.com>
To: Dmitry Dolgov <9erthalion6@gmail.com>
To: Andy Fan <zhihui.fan1213@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Jesper Pedersen <jesper.pedersen@redhat.com>
Cc: David Rowley <dgrowleyml@gmail.com>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Cc: Peter Geoghegan <pg@bowt.ie>
Cc: Thomas Munro <thomas.munro@gmail.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Dilip Kumar <dilipbalaut@gmail.com>
Subject: RE: Index Skip Scan (new UniqueKeys)
Date: Fri, 10 Jul 2020 17:03:37 +0000
Message-ID: <fb18ee881ea24d31b5272b4e5053a6aa@opammb0561.comp.optiver.com> (raw)
In-Reply-To: <20200629120709.52w2zi36mtzyliv2@localhost>
References: <20200609102247.jdlatmfyeecg52fi@localhost>
<CAKU4AWrqYz7-+rr=mGe8w6i=NsAZap6cKe=QoQsYGeb4AuiSaA@mail.gmail.com>
<20200629120709.52w2zi36mtzyliv2@localhost>
Hi Dmitry,
Also took another look at the patch now, and found a case of incorrect data. It looks related to the new way of creating the paths, as I can't recall seeing this in earlier versions.
create table t1 as select a,b,b%5 as c, random() as d from generate_series(1, 10) a, generate_series(1,100) b;
create index on t1 (a,b,c);
postgres=# explain select distinct on (a) * from t1 order by a,b desc,c;
QUERY PLAN
-------------------------------------------------------------------------------
Sort (cost=2.92..2.94 rows=10 width=20)
Sort Key: a, b DESC, c
-> Index Scan using t1_a_b_c_idx on t1 (cost=0.28..2.75 rows=10 width=20)
Skip scan: true
(4 rows)
With the 'order by a, b desc, c' we expect the value of column 'b' to always be 100. With index_skipscan enabled, it always gives 1 though. It's not correct that the planner chooses a skip scan followed by sort in this case.
-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, zhihui.fan1213@gmail.com, jesper.pedersen@redhat.com, dgrowleyml@gmail.com, horikyota.ntt@gmail.com, pg@bowt.ie, thomas.munro@gmail.com, tomas.vondra@2ndquadrant.com, dilipbalaut@gmail.com
Subject: RE: Index Skip Scan (new UniqueKeys)
In-Reply-To: <fb18ee881ea24d31b5272b4e5053a6aa@opammb0561.comp.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