Re: Strange (?) Index behavior?

Allen Landsidel <alandsidel(at)gmail(dot)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