From: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
---|---|
To: | Greg Stark <stark(at)mit(dot)edu>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Dilyan Palauzov <dilyan(dot)palauzov(at)aegee(dot)org>, PostgreSQL Bugs <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: Query planner skipping index depending on DISTINCT parameter order (2) |
Date: | 2017-10-01 22:03:29 |
Message-ID: | eaee8428-8d38-9ee6-b129-a29eeb8a80c2@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On 09/25/2017 12:43 PM, Greg Stark wrote:
> On 17 September 2017 at 18:15, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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_idx on 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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2017-10-02 02:21:36 | Re: BUG #14815: event trigger in extension |
Previous Message | evan.r.rempel | 2017-09-30 22:44:44 | BUG #14839: failed archive log messages are merged as 1 message |