>From: Christian Rengstl
>Subject: [PERFORM] Optimizing PostgreSQL for Windows
>
>Hi list,
>
>I have the following query:
>select t.a1, t.a2 from table1 t inner join table2 s
>using(id) where t.pid='xyz' and s.chromosome=9 order by s.pos;
>
>" -> Bitmap Heap Scan on table1 t (cost=388.25..27357.57
>rows=22286 width=23) (actual time=112.595..32989.663 rows=22864 loops=1)"
>" Recheck Cond: ((pid)::text = 'xyz'::text)"
>" -> Bitmap Index Scan on idx_table1 (cost=0.00..382.67
>rows=22286 width=0) (actual time=103.790..103.790 rows=22864 loops=1)"
>" Index Cond: ((pid)::text = 'xyz'::text)"
The bitmap heap scan on table1 seems very slow. What version of Postgres
are you using? There were performance enhancements in 8.1 and 8.2. What
kind of a hard drive are you using? I would guess a single SATA drive would
give you better performance than that, but I don't know for sure. Do you
regularly vacuum the table? Not enough vacuuming can lead to tables filled
with dead rows, which can increase the amount of data needing to be scanned
considerably.
Dave