counting rows
counting rows
От:
pgboy@guthrie.charm.net
Дата:
i need to get a row count on a large table. it appears that select count(*) from
Re: counting rows
От:
greg@turnstep.com
Дата:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > i need to get a row count on a large table. it appears that > select count(*) from
Re: counting rows
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
pgboy@guthrie.charm.net writes: > so, i am wondering if analyze also does a table scan, It does not --- it does a statistical sampling, and it's quite possible that the estimate will be badly off. regards, tom lane
Re: counting rows
От:
pgboy@guthrie.charm.net
Дата:
nice stuff (the whole presentation, full of stuff i did not know), thanks. of course, a question... the bit that reads: If you know the maximum value of an integer column and have an index on it: SELECT COUNT(*) FROM t2 WHERE c2 < 1000000; made me think that the above would do an index scan vice a table scan. but when i try it with my table (currently has almost 70,000,000 rows) it still does a table scan (at least, EXPLAIN says it will do a table scan) ... well, sometimes. the table has two integer fields, SECONDS and MICROS, and i have created an index on (SECONDS, MICROS). ANALYZEd the table so the index would be considered useful, tested that pg was happy by running: EXPLAIN SELECT count(*) FROM tess WHERE SECONDS < 100; sure enough, query plan says it'll use an index scan. but... EXPLAIN SELECT count(*) from tess where SECONDS < 1100000000; query plan here says it will use a table scan. the cutoff, the highest value at which it will still do an index scan, is 1060183451. given that this value is typically gotten from the system call gettimeofday and the current maximum value is 1060188816 (about noon today), this is, er, problematic. any ideas? thanks. pg On Wed, 6 Aug 2003 greg@turnstep.com wrote: > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > > > i need to get a row count on a large table. it appears that > > select count(*) from