Re: Index Skip Scan
От | Jesper Pedersen |
---|---|
Тема | Re: Index Skip Scan |
Дата | |
Msg-id | 988b7763-5c55-1be1-b7b6-f3a68eff0d40@redhat.com обсуждение исходный текст |
Ответ на | Re: Index Skip Scan (Rafia Sabih <rafia.pghackers@gmail.com>) |
Ответы |
Re: Index Skip Scan
(Dmitry Dolgov <9erthalion6@gmail.com>)
|
Список | 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
В списке pgsql-hackers по дате отправления: