Unstable path in index regress test query
Hi hackers!
While doing some tests I've stumbled upon an unstable behavior of the query
explain
SELECT unique1 FROM tenk1
WHERE unique1 IN (1,42,7)
ORDER BY unique1;
from create_index regress test.
This test could randomly return any of 2 paths (below is sequential run,
clean latest master install, without server restart or any other queries
in-between):
postgres=# explain SELECT unique1 FROM tenk1
WHERE unique1 IN (1,42,7)
ORDER BY unique1;
QUERY PLAN
-------------------------------------------------------------------------------------
Sort (cost=310.65..311.02 rows=150 width=4)
Sort Key: unique1
-> Bitmap Heap Scan on tenk1 (cost=14.02..305.23 rows=150 width=4)
Recheck Cond: (unique1 = ANY ('{1,42,7}'::integer[]))
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..13.98 rows=150
width=0)
Index Cond: (unique1 = ANY ('{1,42,7}'::integer[]))
(6 rows)
postgres=# explain SELECT unique1 FROM tenk1
WHERE unique1 IN (1,42,7)
ORDER BY unique1;
QUERY PLAN
---------------------------------------------------------------------------------
Index Only Scan using tenk1_unique1 on tenk1 (cost=0.29..12.91 rows=3
width=4)
Index Cond: (unique1 = ANY ('{1,42,7}'::integer[]))
(2 rows)
The strange thing is that paths are returned quite randomly, but the first
more often than the second.
Is it a bug? Or some feature?
You could check the full log in attach.
Thanks!
--
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/
Attachments:
Nikita Malakhov <hukutoc@gmail.com> writes:
While doing some tests I've stumbled upon an unstable behavior of the query
explain
SELECT unique1 FROM tenk1
WHERE unique1 IN (1,42,7)
ORDER BY unique1;
from create_index regress test.
I wouldn't call what you're showing here an instability.
You create and populate the table, but your test script
isn't giving autovacuum any time to catch up, so the
first EXPLAIN is based on default behaviors without stats.
Then when you do an ANALYZE, the plan changes because the
rowcount estimates change.
You probably should make that a VACUUM ANALYZE, actually,
to ensure that the whole table is marked all-visible.
That can affect the estimated cost of an index-only scan too.
regards, tom lane
Hi,
Yes, the VACUUM ANALYZE does it, I forgot that the vacuum
could affect estimations.
Tom, thank you for the clarification!
--
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/