Hi,
I'm running PostgreSQL version 8 on a dual 2.4GHz Xeon with 1GB of RAM
and an IDE hard drive. My big table has around 9 million records.
Is there a tuning parameter I can change to increase speed of selects?
Clearly, there's already some buffering going on since selecting an
indexed ~50,000 records takes 17 seconds on the first try, and only 0.5
seconds on the second try (from pgsql).
cowpea=> explain analyze select bs_fk from blast_result where
si_fk=11843254;
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using si_fk_index on blast_result (cost=0.00..22874.87
rows=58118 width=4) (actual time=112.249..17472.935 rows=50283 loops=1)
Index Cond: (si_fk = 11843254)
Total runtime: 17642.522 ms
(3 rows)
cowpea=> explain analyze select bs_fk from blast_result where
si_fk=11843254;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Index Scan using si_fk_index on blast_result (cost=0.00..22874.87
rows=58118 width=4) (actual time=0.178..341.643 rows=50283 loops=1)
Index Cond: (si_fk = 11843254)
Total runtime: 505.011 ms
(3 rows)
cowpea=>
Thanks,
Tom
--
Tom Laudeman
twl8n@virginia.edu
(434) 924-2456
http://www.people.virginia.edu/~twl8n/
http://laudeman.com/