[BUGS] Query planner skipping index depending on DISTINCT parameter order
От | Дилян Палаузов |
---|---|
Тема | [BUGS] Query planner skipping index depending on DISTINCT parameter order |
Дата | |
Msg-id | 3c651d99-a943-4e21-4ec9-604b142c85e0@aegee.org обсуждение исходный текст |
Ответы |
[BUGS] Query planner skipping index depending on DISTINCT parameter order(2)
(Дилян Палаузов<dpa-postgres@aegee.org>)
|
Список | 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
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Pierre-Emmanuel AndréДата:
Сообщение: Re: [BUGS] BUG #14814: Documentation errors for OpenBSD