Statistics visibility in SERIALIZABLE transactions

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

Responses

Browse pgsql-hackers by date

  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]