From: Silke Trissl <trissl@informatik.hu-berlin.de>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Tell postgres which index to use?
Date: Wed, 09 Feb 2005 18:58:02 +0100
Message-ID: <420A4F2A.1060506@informatik.hu-berlin.de> (raw)
In-Reply-To: <200502090941.01447.josh@agliodbs.com>
References: <420A3309.5040507@informatik.hu-berlin.de>
<200502090941.01447.josh@agliodbs.com>
Sorry,
>
>>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)
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: trissl@informatik.hu-berlin.de, josh@agliodbs.com
Subject: Re: Tell postgres which index to use?
In-Reply-To: <420A4F2A.1060506@informatik.hu-berlin.de>
* 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