Query planner skipping index depending on DISTINCT parameter order (2)

Post Scriptum to my email from yesterday.

I deleted the_index, so that I have now

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)

and as you can see, there is still a btree index on (id, token). I expect the same query plans, as before deleting the_index, as bayes_token_pkey has the same information as the_index had. This does not happen on my system and I have not tweaked the default configuration files.

spamassassin=> EXPLAIN ANALYZE SELECT DISTINCT(id, token) token FROM bayes_token;
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Unique (cost=21737.73..22555.11 rows=163476 width=32) (actual time=914.275..1206.239 rows=165523 loops=1) │
│ -> Sort (cost=21737.73..22146.42 rows=163476 width=32) (actual time=914.274..1125.674 rows=165523 loops=1) │
│ Sort Key: (ROW(id, token)) │
│ Sort Method: external merge Disk: 6616kB │
│ -> Seq Scan on bayes_token (cost=0.00..3668.76 rows=163476 width=32) (actual time=0.015..50.849 rows=165523 loops=1) │
│ Planning time: 0.079 ms │
│ Execution time: 1216.047 ms │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

What is the difference makes ROW between the one case here
Sort Key: (ROW(id, token))
and in the other below:
Sort Key: token, id

Regards
Dilyan

On 09/14/17 17:38, Дилян Палаузов wrote:
> Hello,
>
> I have this database in Pg 9.6.5, the schema comes from spamassassin:
>
> ┌────────────┬─────────┬──────────────────────────────┐
> │ 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)
> "the_index" btree (id, token)
>
> with 261000 rows. According to my understanding, "SELECT DISTINCT ON(token, id) token FROM bayes_token;" and "SELECT DISTINCT ON(id, token) token FROM bayes_token;" are supposed to deliver the same results, as only the "token" and "id" columns are exchanged in DISTINCT, so the query optimizer is supposed to generate the same query plan. But it does not:
>
> EXPLAIN ANALYZE SELECT DISTINCT ON(token, id) token FROM bayes_token;
> ┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
> │ QUERY PLAN │
> ├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
> │ Unique (cost=32624.56..34588.73 rows=261889 width=10) (actual time=154.947..264.439 rows=261935 loops=1) │
> │ -> Sort (cost=32624.56..33279.28 rows=261889 width=10) (actual time=154.945..215.378 rows=261935 loops=1) │
> │ Sort Key: token, id │
> │ Sort Method: external merge Disk: 5624kB │
> │ -> Seq Scan on bayes_token (cost=0.00..4579.89 rows=261889 width=10) (actual time=0.015..35.282 rows=261935 loops=1) │
> │ Planning time: 0.085 ms │
> │ Execution time: 285.303 ms │
> └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
>
> EXPLAIN ANALYZE SELECT DISTINCT ON(id, token) token FROM bayes_token;
> ┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
> │ QUERY PLAN │
> ├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
> │ Unique (cost=0.42..13819.07 rows=262023 width=10) (actual time=0.028..207.695 rows=262018 loops=1) │
> │ -> Index Only Scan using the_index on bayes_token (cost=0.42..12508.95 rows=262023 width=10) (actual time=0.026..147.882 rows=262018 loops=1) │
> │ Heap Fetches: 261729 │
> │ Planning time: 0.086 ms │
> │ Execution time: 232.598 ms │
> └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
>
> The_index and the SELECTs are invented for the sake of this demonstration.
>
> Regards
> Diluan