On Mon, Oct 10, 2011 at 10:36 AM, Kevin Grittner <kevin.gritt...@wicourts.gov> wrote: > Bruce Momjian <br...@momjian.us> wrote: >> I talked to Robert Haas and he said that index-only scans do not >> optimize COUNT(*). Is this something we can do for PG 9.2? Is >> anyone working on this? > > Well, it's not that it doesn't optimize COUNT(*) -- it's that it > doesn't yet cost the index scan as cheaper than a table scan when > you're accessing every row. > > create table t (id int not null primary key); > insert into t select generate_series(1, 1000000); > vacuum freeze analyze; > explain analyze select count(*) from t > where id between 500000 and 500010; > > That gives you an index-only scan; but without the WHERE clause it > uses a seq scan.
If you convert the where clause to "where id is not null" it uses the index only scan again, but only if you nudge it too with enable_seqscan=off. I'm not sure why it needs the nudge in one case but not the other. Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
