strange row number estimates in pg9.1rc1

Поиск
Список
Период
Сортировка
От Sergey E. Koposov
Тема strange row number estimates in pg9.1rc1
Дата
Msg-id alpine.LRH.2.00.1108290904330.19210@lnfm1.sai.msu.ru
обсуждение исходный текст
Ответы Re: strange row number estimates in pg9.1rc1  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-hackers
Hello hackers,

I'm seeing something weird which looks like a bug in 9.1rc1 after the 
upgrade 8.4->9.0->9.1 done using pg_upgrade.

I have a set of *static* tables for which "explain select * " gives 
row number estimates which are an order of magnitude lower than the actual 
number of rows in a table (despite the vacuum analyze executed  on a 
table immediately before). See:

wsdb=> vacuum verbose analyze ukidssdr7.lassource;
INFO:  vacuuming "ukidssdr7.lassource"
INFO:  index "ukidssdr7lassource_q3c_idx" now contains 58060655 row 
versions in
143515 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.55s/0.19u sec elapsed 3.23 sec.
.........
INFO:  "lassource": found 0 removable, 0 nonremovable row versions in 0 
out of 6451184 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 2.66s/0.94u sec elapsed 17.92 sec.
INFO:  analyzing "ukidssdr7.lassource"
INFO:  "lassource": scanned 30000 of 6451184 pages, containing 270000 live 
rows and 0 dead rows; 30000 rows in sample, 806239 estimated total rows

wsdb=> explain select * from ukidssdr7.lassource ;                              QUERY PLAN
---------------------------------------------------------------------- Seq Scan on lassource  (cost=0.00..6459246.39
rows=806239width=766)
 

wsdb=> select count(*) from ukidssdr7.lassource ;  count
---------- 58060655
(1 row)

All the columns in that table have fixed width types (e.g. 
real,int,bigint etc; no varchars, texts). So I don't see the reason why 
the row number estimate must be so much off. I also checked that the size 
of the relation is almost exactly equal to  width * count(*) = 
766*58060655. So there is no empty space anywhere in the relation ( as it 
should be because there was completely no write activity on the table).

And I noticed that at least for several tables with hundreds of millions 
rows, explain select * shows ridiculously small number of expected rows:
wsdb=> explain select * from sdssdr7.phototag ;                              QUERY PLAN
---------------------------------------------------------------------- Seq Scan on phototag  (cost=0.00..24408626.00
rows=720000width=288)
 

I guess it may be important that I did upgrade the cluster from 8.4 to 
9.0 and to 9.1 using pg_upgrade. vacuum analyze have been run on the cluster. 
after the ugprades.

Am i missing something or is it a bug ? it looks to me like some 
arithmetic error in the computation of the number of rows in the tables.
At least before when I was using PG 8.4 for the same data, I was used to
do explain select * to get the number of rows in the tables, instead of 
count(*) (my tables are very large), now it seems that there is a huge 
discrepancy between the numbers.

Thanks,    Sergey

*******************************************************************
Sergey E. Koposov, PhD
Institute for Astronomy, Cambridge/Sternberg Astronomical Institute
Web: http://lnfm1.sai.msu.ru/~math
E-mail: math@sai.msu.ru


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

Предыдущее
От: Robert Haas
Дата:
Сообщение: Re: spinlocks on HP-UX
Следующее
От: Noah Misch
Дата:
Сообщение: Re: PATCH: regular logging of checkpoint progress