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/


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq