[BUGS] Re: Query planner skipping index depending on DISTINCT parameterorder (2)

On 09/25/2017 12:43 PM, Greg Stark wrote:
> On 17 September 2017 at 18:15, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
>> For plain DISTINCT, yeah we could consider other orderings ... but
>> we're rather unlikely to find an index that matches all the output
>> columns, regardless of what order they're in.  So it's just not that
>> exciting.
> 
> I don't follow this part. Are you saying a simple "select distinct a,b
> from table" is unlikely to find a matching index on <b,a>? Don't we
> already do exactly this for "select a,b from table group by a,b"?

FWIW we don't do that:
   create table t (a int, b int);   insert into t select mod(i, 1000), mod(i, 1000)                  from
generate_series(1,1000000)S(i);   create index on t (a,b);   vacuum analyze t;
 
   explain (costs off) select a, b, count(*) from t group by a , b;                    QUERY PLAN
--------------------------------------------   GroupAggregate      Group Key: a, b      ->  Index Only Scan using
t_a_b_idxon t   (3 rows)
 
   explain (costs off) select a, b, count(*) from t group by b , a;           QUERY PLAN   ---------------------------
 GroupAggregate      Group Key: b, a      ->  Sort            Sort Key: b, a            ->  Seq Scan on t   (5 rows)
 

But yeah, fixing this is probably more worthwhile than the original
DISTINCT ON issue ...

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs