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
