From: | Jesper Pedersen <jesper(dot)pedersen(at)redhat(dot)com> |
---|---|
To: | Rafia Sabih <rafia(dot)pghackers(at)gmail(dot)com>, Floris Van Nee <florisvannee(at)optiver(dot)com> |
Cc: | Dmitry Dolgov <9erthalion6(at)gmail(dot)com>, Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>, Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Thomas Munro <thomas(dot)munro(at)gmail(dot)com>, Alexander Kuzmenkov <a(dot)kuzmenkov(at)postgrespro(dot)ru>, Peter Geoghegan <pg(at)bowt(dot)ie>, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>, Bhushan Uparkar <bhushan(dot)uparkar(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>, James Coleman <jtc331(at)gmail(dot)com> |
Subject: | Re: Index Skip Scan |
Date: | 2019-06-03 18:16:45 |
Message-ID: | 988b7763-5c55-1be1-b7b6-f3a68eff0d40@redhat.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi Rafia,
On 6/1/19 6:03 AM, Rafia Sabih wrote:
> Here is my repeatable test case,
>
> create table t (market text, feedcode text, updated_at timestamptz,
> value float8) ;
> create index on t (market, feedcode, updated_at desc);
> insert into t values('TEST', 'abcdef', (select timestamp '2019-01-10
> 20:00:00' + random() * (timestamp '2014-01-20 20:00:00' - timestamp
> '2019-01-20 20:00:00') ), generate_series(1,100)*9.88);
> insert into t values('TEST', 'jsgfhdfjd', (select timestamp
> '2019-01-10 20:00:00' + random() * (timestamp '2014-01-20 20:00:00' -
> timestamp '2019-01-20 20:00:00') ), generate_series(1,100)*9.88);
>
> Now, without the patch,
> select distinct on (market, feedcode) market, feedcode from t where
> market='TEST';
> market | feedcode
> --------+-----------
> TEST | abcdef
> TEST | jsgfhdfjd
> (2 rows)
> explain select distinct on (market, feedcode) market, feedcode from t
> where market='TEST';
> QUERY PLAN
> ----------------------------------------------------------------
> Unique (cost=12.20..13.21 rows=2 width=13)
> -> Sort (cost=12.20..12.70 rows=201 width=13)
> Sort Key: feedcode
> -> Seq Scan on t (cost=0.00..4.51 rows=201 width=13)
> Filter: (market = 'TEST'::text)
> (5 rows)
>
> And with the patch,
> select distinct on (market, feedcode) market, feedcode from t where
> market='TEST';
> market | feedcode
> --------+----------
> TEST | abcdef
> (1 row)
>
> explain select distinct on (market, feedcode) market, feedcode from t
> where market='TEST';
> QUERY PLAN
> ------------------------------------------------------------------------------------------------
> Index Only Scan using t_market_feedcode_updated_at_idx on t
> (cost=0.14..0.29 rows=2 width=13)
> Scan mode: Skip scan
> Index Cond: (market = 'TEST'::text)
> (3 rows)
>
> Notice that in the explain statement it shows correct number of rows
> to be skipped.
>
Thanks for your test case; this is very helpful.
For now, I would like to highlight that
SET enable_indexskipscan = OFF
can be used for testing with the patch applied.
Dmitry and I will look at the feedback provided.
Best regards,
Jesper
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2019-06-03 19:15:04 | Re: Pinned files at Windows |
Previous Message | Jesper Pedersen | 2019-06-03 18:13:56 | Re: Index Skip Scan |