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

Hello,

for the record, in this table

spamassassin=> \d bayes_token           Table "public.bayes_token"  Column    Type            Modifiers
────────── ─────── ────────────────────────────
id         integer not null default 0
token      bytea   not null default '\x'::bytea
spam_count integer not null default 0
ham_count  integer not null default 0
atime      integer not null default 0
Indexes:    "bayes_token_pkey" PRIMARY KEY, btree (id, token)    "bayes_token_idx1" btree (token)


"SELECT id, token FROM bayes_token" and "SELECT id, token FROM bayes_token WHERE id > 0" do Seq Scan and need 35-50
Sec.

But "SELECT id, token FROM bayes_token WHERE id > 1" uses Index Only Scan and 0.04 seconds on pg 9.6.5 .

Greetings  Dilian

On 09/25/17 12:43, 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"? I
> would have expected the two equivalent SQL statements to use exactly
> the same infrastructure and thought it was only a matter of historical
> legacy that they didn't.
> 


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