Statistics visibility in SERIALIZABLE transactions

Поиск
Список
Период
Сортировка
От Michael Fuhr
Тема Statistics visibility in SERIALIZABLE transactions
Дата
Msg-id 20061120055457.GA65698@winnie.fuhr.org
обсуждение исходный текст
Ответы Re: Statistics visibility in SERIALIZABLE transactions  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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.25rows=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.62rows=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


В списке pgsql-hackers по дате отправления:

Предыдущее
От: "Gurjeet Singh"
Дата:
Сообщение: Re: [Fwd: Index Advisor]
Следующее
От: "Brendan Jurd"
Дата:
Сообщение: psql: present working directory