strange row number estimates in pg9.1rc1

From: "Sergey E(dot) Koposov" <math(at)sai(dot)msu(dot)ru>
To: pgsql-hackers(at)postgresql(dot)org
Subject: strange row number estimates in pg9.1rc1
Date: 2011-08-29 05:47:21
Message-ID: alpine.LRH.2.00.1108290904330.19210@lnfm1.sai.msu.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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=806239 width=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=720000 width=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(at)sai(dot)msu(dot)ru

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Noah Misch 2011-08-29 05:47:56 Re: PATCH: regular logging of checkpoint progress
Previous Message YAMAMOTO Takashi 2011-08-29 00:28:34 Re: tab stop in README