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 по дате отправления: