What about creating views on areas of the table that are queried often?
I don't know if you have access or the ability to find what type of
trends the table has, in terms of queries, but if you create some views
on frequently visited information, this could also help.
Tom Laudeman wrote:
> 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
>
>