is there a way to tell Postgres which index to use when a query is issued in 7.4.2?
PostgreSQL adjusts usage through global parameters, statistics, and periodic ANALYZE. Please post an EXPLAIN ANALYZE (not just EXPLAIN) for your query and people on this list can help you with your specific problem.
here are the plans, but still I would like to tell Postgres to use an index or the join method (like HINT in ORACLE).
First the vacuum db=# vacuum full analyze; VACUUM
Then the query for the first time with analyze
db=# EXPLAIN ANALYZE
db-# SELECT chain.pdb_id, chain.id FROM PDB_ENTRY, CHAIN
WHERE PDB_ENTRY.resolution > 0.0 and PDB_ENTRY.resolution < 1.7
AND PDB_ENTRY.id = CHAIN.pdb_id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=1426.75..5210.52 rows=7533 width=8) (actual time=77.712..399.108 rows=5798 loops=1)
Hash Cond: ("outer".pdb_id = "inner".id)
-> Seq Scan on "chain" (cost=0.00..3202.11 rows=67511 width=8) (actual time=0.048..151.885 rows=67511 loops=1)
-> Hash (cost=1418.68..1418.68 rows=3226 width=4) (actual time=77.062..77.062 rows=0 loops=1)
-> Seq Scan on pdb_entry (cost=0.00..1418.68 rows=3226 width=4) (actual time=0.118..71.956 rows=3329 loops=1)
Filter: ((resolution > 0::double precision) AND (resolution < 1.7::double precision))
Total runtime: 404.434 ms
(7 rows)
And then try to avoid the Hash Join
db=# SET ENABLE_hashjoin = OFF;
SET
db=# EXPLAIN ANALYZE
db-# SELECT chain.pdb_id, chain.id FROM PDB_ENTRY, CHAIN
WHERE PDB_ENTRY.resolution > 0.0 and PDB_ENTRY.resolution < 1.7
AND PDB_ENTRY.id = CHAIN.pdb_id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=9163.85..11100.74 rows=7533 width=8) (actual time=606.505..902.740 rows=5798 loops=1)
Merge Cond: ("outer".id = "inner".pdb_id)
-> Index Scan using pdb_entry_pkey on pdb_entry (cost=0.00..1516.03 rows=3226 width=4) (actual time=0.440..102.912 rows=3329 loops=1)
Filter: ((resolution > 0::double precision) AND (resolution < 1.7::double precision))
-> Sort (cost=9163.85..9332.63 rows=67511 width=8) (actual time=605.838..694.190 rows=67501 loops=1)
Sort Key: "chain".pdb_id
-> Seq Scan on "chain" (cost=0.00..3202.11 rows=67511 width=8) (actual time=0.064..225.859 rows=67511 loops=1)
Total runtime: 911.024 ms
(8 rows)
And finally timewise the fastest method, but not costwise. Even for almost full table joins, this method is the fastest.
db=# SET ENABLE_mergejoin = off;
SET
db=# EXPLAIN ANALYZE
db-# SELECT chain.pdb_id, chain.id FROM PDB_ENTRY, CHAIN
WHERE PDB_ENTRY.resolution > 0.0 and PDB_ENTRY.resolution < 1.7
AND PDB_ENTRY.id = CHAIN.pdb_id;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..23849.81 rows=7533 width=8) (actual time=0.341..198.162 rows=5798 loops=1)
-> Seq Scan on pdb_entry (cost=0.00..1418.68 rows=3226 width=4) (actual time=0.145..78.177 rows=3329 loops=1)
Filter: ((resolution > 0::double precision) AND (resolution < 1.7::double precision))
-> Index Scan using chain_pdb_id_ind on "chain" (cost=0.00..6.87 rows=6 width=8) (actual time=0.021..0.027 rows=2 loops=3329)
Index Cond: ("outer".id = "chain".pdb_id)
Total runtime: 204.105 ms
(6 rows)
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
