[BUGS] Query planner skipping index depending on DISTINCT parameter order - Mailing list pgsql-bugs

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
DISTINCTON(id, token) token FROM bayes_token;" are supposed to deliver the same results, as only the "token" and "id"
columnsare 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.882rows=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


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