From: Tom Lane <tgl@sss.pgh.pa.us>
To: Carlos Moreno <moreno_pg@mochima.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Seqscan/Indexscan still a known issue?
Date: Sat, 27 Jan 2007 12:28:11 -0500
Message-ID: <6888.1169918891@sss.pgh.pa.us> (raw)
In-Reply-To: <45BB873C.4000304@mochima.com>
References: <45BAB682.6060709@mochima.com>
<45BB214E.3090907@fuzzy.cz>
<45BB873C.4000304@mochima.com>
Carlos Moreno <moreno_pg@mochima.com> writes:
> But I think the problem is that this particular table had not been
> vacuum analyzed after having inserted the 20000 records (the
> query planner was giving me seq. scan when the table had about
> a dozen records --- and seq. scan was, indeed, 10 times faster;
> as a test, to make sure that the query planner would do the right
> thing when the amount of records was high, I inserted 20000
> records, and tried again --- now the seq. scan was 100 times
> slower, but it was still chosen (at that point was that I did a
> search through the archives and then posted the question).
> But now, after reading the replies, I did a vacuum analyze for
> this table, and now the query planner is choosing the Index
> scan.
One reason you might consider updating is that newer versions check the
physical table size instead of unconditionally believing
pg_class.relpages/reltuples. Thus, they're much less likely to get
fooled when a table has grown substantially since it was last vacuumed
or analyzed.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, moreno_pg@mochima.com
Subject: Re: Seqscan/Indexscan still a known issue?
In-Reply-To: <6888.1169918891@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox