From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Statistics visibility in SERIALIZABLE transactions |
Date: | 2006-11-20 05:54:57 |
Message-ID: | 20061120055457.GA65698@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Updates to planner statistics appear to be visible in SERIALIZABLE
transactions even though updated data is not. Is this intentional?
Could that adversely affect query plans?
CREATE TABLE test (x integer);
INSERT INTO test (x) SELECT i % 2 FROM generate_series(1, 100) AS g(i);
ANALYZE test;
Transaction 1:
BEGIN ISOLATION LEVEL SERIALIZABLE;
EXPLAIN ANALYZE SELECT * FROM test WHERE x = 1;
QUERY PLAN
------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..2.25 rows=50 width=4) (actual time=0.201..0.787 rows=50 loops=1)
Filter: (x = 1)
Total runtime: 1.169 ms
(3 rows)
Transaction 2:
BEGIN;
DELETE FROM test WHERE x = 1;
ANALYZE test;
COMMIT;
Transaction 1:
EXPLAIN ANALYZE SELECT * FROM test WHERE x = 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..1.62 rows=1 width=4) (actual time=0.499..1.090 rows=50 loops=1)
Filter: (x = 1)
Total runtime: 1.476 ms
(3 rows)
In Transaction 1's second query the planner uses an updated row
count estimate even though the old rows are still visible. I think
I understand why statistics like the total relation size and total
number of tuples would help the planner, but is there a reason for
distribution statistics to be visible for data that itself isn't
visible?
Thanks.
--
Michael Fuhr
From | Date | Subject | |
---|---|---|---|
Next Message | Brendan Jurd | 2006-11-20 06:09:57 | psql: present working directory |
Previous Message | Gurjeet Singh | 2006-11-20 05:35:46 | Re: [Fwd: Index Advisor] |