pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Floris Van Nee <florisvannee@Optiver.com>
To: Dmitry Dolgov <9erthalion6@gmail.com>
Cc: David Rowley <david.rowley@2ndquadrant.com>
Cc: James Coleman <jtc331@gmail.com>
Cc: jesper.pedersen@redhat.com <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: Peter Geoghegan <pg@bowt.ie>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Alexander Korotkov <a.korotkov@postgrespro.ru>
Subject: Re: Index Skip Scan
Date: Sat, 22 Jun 2019 22:15:17 +0000
Message-ID: <1561241730766.23014@Optiver.com> (raw)
In-Reply-To: <CA+q6zcXV3cPWPEuZsPdN4jSOcBZifxhuY=cA3+QPMj+622kr5Q@mail.gmail.com>
References: <2685f020-f302-9bf6-1b5c-ebc2bcebc635@2ndquadrant.com>
	<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>
	<1559763570329.85227@Optiver.com>
	<4d469bf2-49ff-bc3d-a428-6c7b5d87d526@redhat.com>
	<CAKJS1f86FgODuUnHiQ25RKeuES4qTqeNxm1QbqJWrBoZxVGLiQ@mail.gmail.com>
	<CA+q6zcXoYjCtyU-jFU_=p2DL_K3BHbBEEbJHaJTxdvRX3cU-Sg@mail.gmail.com>
	<CA+q6zcVVigO_1KRBxvzKfnR593CEE7FKR-bwP-XcH6AVHOo4+g@mail.gmail.com>
	<a8b5c2d9-7b23-e12b-fc6b-eee63dc8dacf@redhat.com>
	<1561198638439.56867@Optiver.com>
	<CA+q6zcXV3cPWPEuZsPdN4jSOcBZifxhuY=cA3+QPMj+622kr5Q@mail.gmail.com>


> Thanks for testing! You're right, looks like in the current implementation in
> case of backwards scan there is one unnecessary extra step forward. It seems
> this mistake was made, since I was concentrating only on the backward scans
> with a cursor, and used not exactly correct approach to wrap up after a scan
> was finished. Give me a moment, I'll tighten it up.

Thanks. Looking forward to it. I think I found some other strange behavior. Given the same table as in my previous e-mail, the following queries also return inconsistent results. I spent some time trying to debug it, but can't easily pinpoint the cause. It looks like it also skips over one value too much, my guess is during _bt_skippage call in _bt_skip?
Perhaps a question: when stepping through code in GDB, is there an easy way to pretty print for example the contents on an IndexTuple? I saw there's some tools out there that can pretty print plans, but viewing tuples is more complicated I guess.

-- this one is OK
postgres=# select distinct on (a) a,b from a where b>1;
 a | b
---+---
 1 | 2
 2 | 2
 3 | 2
 4 | 2
 5 | 2
(5 rows)

-- this one is not OK, it seems to skip too much
postgres=# select distinct on (a) a,b from a where b=2;
 a | b
---+---
 1 | 2
 3 | 2
 5 | 2
(3 rows)

view thread (248+ messages)  latest in thread

Message-ID: <1561241730766.23014@Optiver.com>
Permalink:  ../1561241730766.23014@Optiver.com/
Also on:    postgresql.org/message-id/1561241730766.23014@Optiver.com

 · 

reply
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, david.rowley@2ndquadrant.com, jtc331@gmail.com, jesper.pedersen@redhat.com, rafia.pghackers@gmail.com, jeff.janes@gmail.com, thomas.munro@gmail.com, pg@bowt.ie, tomas.vondra@2ndquadrant.com, thomas.munro@enterprisedb.com, a.korotkov@postgrespro.ru
  Subject: Re: Index Skip Scan
  In-Reply-To: <1561241730766.23014@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