From: Greg Stark <gsstark@mit.edu>
To: Allen Landsidel <alandsidel@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Strange (?) Index behavior?
Date: 11 Nov 2004 15:49:46 -0500
Message-ID: <873bzgdsb9.fsf@stark.xeocode.com> (raw)
In-Reply-To: <88f1825a041111082176f13033@mail.gmail.com>
References: <88f1825a041105095631064cb0@mail.gmail.com>
<008401c4c366$127d5260$8300a8c0@solent>
<88f1825a04110513028759dd3@mail.gmail.com>
<418C1757.70909@ymogen.net>
<25492.1099713863@sss.pgh.pa.us>
<88f1825a04110523276f83653b@mail.gmail.com>
<88f1825a041111082176f13033@mail.gmail.com>
Allen Landsidel <alandsidel@gmail.com> writes:
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------------
> Index Scan using sname_unique on "testtable" (cost=0.00..34453.74
> rows=8620 width=20) (actual time=77.004..537065.079 rows=74612
> loops=1)
> Index Cond: ((sname >= 'AA'::text) AND (sname < 'AB'::text))
> Filter: (sname ~~ 'AA%'::text)
> Total runtime: 537477.737 ms
> (4 rows)
>
> Time: 537480.571 ms
Nothing you're going to do to the query is going to come up with a more
effective plan than this. It's using the index after all. It's never going to
be lightning fast because it has to process 75k rows.
However 75k rows shouldn't be taking nearly 10 minutes. It should be taking
about 10 seconds.
The 77ms before finding the first record is a bit suspicious. Have you
vacuumed this table regularly? Try a VACUUM FULL VERBOSE, and send the
results. You might try to REINDEX it as well, though I doubt that would help.
Actually you might consider clustering the table on sname_unique. That would
accomplish the same thing as the VACUUM FULL command and also speed up the
index scan. And the optimizer knows (if you analyze afterwards) it so it
should be more likely to pick the index scan. But currently you have to rerun
cluster periodically.
--
greg
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: gsstark@mit.edu, alandsidel@gmail.com
Subject: Re: Strange (?) Index behavior?
In-Reply-To: <873bzgdsb9.fsf@stark.xeocode.com>
* 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