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; With the following output from analyze: "Sort (cost=35075.03..35077.51 rows=991 width=14) (actual time=33313.718..33321.935 rows=22599 loops=1)" " Sort Key: s.pos" " -> Hash Join (cost=7851.48..35025.71 rows=991 width=14) (actual time=256.513..33249.701 rows=22599 loops=1)" " Hash Cond: ((t.id)::text = (s.id)::text)" " -> 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)" " -> Hash (cost=7180.62..7180.62 rows=22609 width=17) (actual time=143.867..143.867 rows=22864 loops=1)" " -> Bitmap Heap Scan on table2 s (cost=333.00..7180.62 rows=22609 width=17) (actual time=108.715..126.637 rows=22864 loops=1)" " Recheck Cond: ((chromosome)::text = '9'::text)" " -> Bitmap Index Scan on idx_table2 (cost=0.00..327.35 rows=22609 width=0) (actual time=108.608..108.608 rows=22864 loops=1)" " Index Cond: ((chromosome)::text = '9'::text)" My OS is Windows 2003 with 4GB Ram and Xeon Duo with 3.2 GHz; shared_buffers is set to 32MB (as I read it should be fairly low on Windows) and work_mem is set to 2500MB, but nevertheless the query takes about 38 seconds to finish. The table "table1" contains approx. 3 million tuples and table2 approx. 500.000 tuples. If anyone could give an advice on either how to optimize the settings in postgresql.conf or anything else to make this query run faster, I really would appreciate. Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
