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

help / color / mirror / Atom feed
From: Alexander Kuzmenkov <a.kuzmenkov@postgrespro.ru>
To: Dmitry Dolgov <9erthalion6@gmail.com>
To: Jesper Pedersen <jesper.pedersen@redhat.com>
Cc: pg@bowt.ie, 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: jtc331@gmail.com
Subject: Re: Index Skip Scan
Date: Wed, 21 Nov 2018 18:38:55 +0300
Message-ID: <b360894b-b436-b3aa-e839-97f1c0413713@postgrespro.ru> (raw)
In-Reply-To: <CA+q6zcW4vZrRivgX47yv+uh+ND0A09kMz4yCa-N6jY69Vs-VjQ@mail.gmail.com>
References: <707b6f68-16fa-7aa7-96e5-eeb4865e6a30@redhat.com>
	<89F84E17-D684-4717-B66F-0F05CB00168E@gmail.com>
	<CAEepm=1ikXHVgSNruQ1HydDZm3OLnLNb5qT2c8S=Mh-VsFQXcQ@mail.gmail.com>
	<CAH2-WznxAN940e=2D8Auz06d=_ZK6F5LVH0HUMbCXGAa=Tirpw@mail.gmail.com>
	<CAEepm=0S1JUUwKCK4jhmtTWm9VUTsCe3+b5tSvddpMpkzas7Hw@mail.gmail.com>
	<CAH2-Wzkk0+vSAiaRSkX84KR0SofKkY1yGHEcLoRBXT-JjM37bw@mail.gmail.com>
	<CA+q6zcV-wgnSMu-YOEmyXVckncQyriiKtUZXm=Gx2s+FV=5MDw@mail.gmail.com>
	<c87d41ae-7c18-a3a1-96f2-f366a43412dc@redhat.com>
	<522092c7-3cb7-a1ec-410b-dfa97ae722f3@postgrespro.ru>
	<11b7c9d1-ae00-4385-97b2-fcd45f0a1a71@redhat.com>
	<3cfd317e-2a8b-8681-0833-8f2ead3af4d4@postgrespro.ru>
	<CA+q6zcUW=GXiw24iiLee3ZhA4JY4wc1hmGE8+20v-W47sOQjNA@mail.gmail.com>
	<cc30955d-2ee9-4c36-e5d7-fc5b552fbbb6@redhat.com>
	<aa0e6d35-5772-d5cb-04b7-65166eaa399f@postgrespro.ru>
	<b4835174-f383-c912-dd7f-2961b69eed1a@redhat.com>
	<CA+q6zcW4vZrRivgX47yv+uh+ND0A09kMz4yCa-N6jY69Vs-VjQ@mail.gmail.com>

On 11/18/18 02:27, Dmitry Dolgov wrote:
>
> [0001-Index-skip-scan-v4.patch]

I ran a couple of tests on this, please see the cases below. As before, 
I'm setting total_cost = 1 for index skip scan so that it is chosen. 
Case 1 breaks because we determine the high key incorrectly, it is the 
second tuple on page or something like that, not the last tuple. Case 2 
is backwards scan, I don't understand how it is supposed to work. We 
call _bt_search(nextKey = ScanDirectionIsForward), so it seems that it 
just fetches the previous tuple like the regular scan does.

case 1:
# create table t as select generate_series(1, 1000000) a;
# create index ta on t(a);
# explain select count(*) from (select distinct a from t) d;
                                QUERY PLAN
-------------------------------------------------------------------------
  Aggregate  (cost=3.50..3.51 rows=1 width=8)
    ->  Index Only Scan using ta on t  (cost=0.42..1.00 rows=200 width=4)
          Scan mode: Skip scan
(3 rows)

postgres=# select count(*) from (select distinct a from t) d;
  count
--------
  500000 -- should be 1kk
(1 row)


case 2:
# create table t as select generate_series(1, 1000000) / 2 a;
# create index ta on t(a);
# explain select count(*) from (select distinct a from t order by a desc) d;
                                      QUERY PLAN
-------------------------------------------------------------------------------------
  Aggregate  (cost=5980.81..5980.82 rows=1 width=8)
    ->  Index Only Scan Backward using ta on t  (cost=0.42..1.00 
rows=478385 width=4)
          Scan mode: Skip scan
(3 rows)

# select count(*) from (select distinct a from t order by a desc) d;
  count
--------
  502733 -- should be 500k

(1 row)


-- 

Alexander Kuzmenkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company





view thread (248+ messages)  latest in thread

Message-ID: <b360894b-b436-b3aa-e839-97f1c0413713@postgrespro.ru>
Permalink:  ../b360894b-b436-b3aa-e839-97f1c0413713@postgrespro.ru/
Also on:    postgresql.org/message-id/b360894b-b436-b3aa-e839-97f1c0413713@postgrespro.ru

 · 

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: a.kuzmenkov@postgrespro.ru, 9erthalion6@gmail.com, jesper.pedersen@redhat.com, thomas.munro@enterprisedb.com, bhushan.uparkar@gmail.com, a.korotkov@postgrespro.ru, jtc331@gmail.com
  Subject: Re: Index Skip Scan
  In-Reply-To: <b360894b-b436-b3aa-e839-97f1c0413713@postgrespro.ru>

* 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