From: Dave Dutcher <dave@tridecap.com>
To: 'Christian Rengstl' <Christian.Rengstl@klinik.uni-regensburg.de>
To: pgsql-performance@postgresql.org
Subject: Re: Optimizing PostgreSQL for Windows
Date: Tue, 30 Oct 2007 10:13:15 -0500
Message-ID: <015601c81b07$657cae10$8e00a8c0@tridecap.com> (raw)
In-Reply-To: <4727402E.90AD.0080.0@klinik.uni-regensburg.de>
References: <4727402E.90AD.0080.0@klinik.uni-regensburg.de>
>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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dave@tridecap.com, Christian.Rengstl@klinik.uni-regensburg.de
Subject: Re: Optimizing PostgreSQL for Windows
In-Reply-To: <015601c81b07$657cae10$8e00a8c0@tridecap.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox