Allen Landsidel <alandsidel@xxxxxxxxx> 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
