From: Dmitry Dolgov <9erthalion6@gmail.com>
To: Floris Van Nee <florisvannee@Optiver.com>
Cc: 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: Sat, 11 Jul 2020 18:21:03 +0200
Message-ID: <20200711162103.hiygat3k6wu2d25p@localhost> (raw)
In-Reply-To: <fb18ee881ea24d31b5272b4e5053a6aa@opammb0561.comp.optiver.com>
References: <20200609102247.jdlatmfyeecg52fi@localhost>
<CAKU4AWrqYz7-+rr=mGe8w6i=NsAZap6cKe=QoQsYGeb4AuiSaA@mail.gmail.com>
<20200629120709.52w2zi36mtzyliv2@localhost>
<fb18ee881ea24d31b5272b4e5053a6aa@opammb0561.comp.optiver.com>
> On Fri, Jul 10, 2020 at 05:03:37PM +0000, Floris Van Nee wrote:
>
> 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)
Good point, thanks for looking at this. With the latest planner version
there are indeed more possibilities to use skipping. It never occured to
me that some of those paths will still rely on index scan returning full
data set. I'll look in details and add verification to prevent putting
something like this on top of skip scan in the next version.
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: 9erthalion6@gmail.com, florisvannee@Optiver.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: <20200711162103.hiygat3k6wu2d25p@localhost>
* 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