X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4477A8B9E42 for ; Wed, 9 Feb 2005 17:58:30 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 26543-10 for ; Wed, 9 Feb 2005 17:58:10 +0000 (GMT) Received: from mail.informatik.hu-berlin.de (mail.informatik.hu-berlin.de [141.20.20.50]) by svr1.postgresql.org (Postfix) with ESMTP id 7FFD18B9C33 for ; Wed, 9 Feb 2005 17:58:09 +0000 (GMT) Received: from informatik.hu-berlin.de (karotte [141.20.27.143]) (authenticated bits=0) by mail.informatik.hu-berlin.de (8.12.10/8.12.10/INF-2.0-MA-SOLARIS-2.8) with ESMTP id j19Hw209024882 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Wed, 9 Feb 2005 18:58:02 +0100 (MET) Message-ID: <420A4F2A.1060506@informatik.hu-berlin.de> Date: Wed, 09 Feb 2005 18:58:02 +0100 From: Silke Trissl User-Agent: Mozilla/5.0 (X11; U; Linux i686; de-AT; rv:1.4) Gecko/20030624 X-Accept-Language: de-at, de, en-us, en MIME-Version: 1.0 To: Josh Berkus Cc: pgsql-performance@postgresql.org Subject: Re: Tell postgres which index to use? References: <420A3309.5040507@informatik.hu-berlin.de> <200502090941.01447.josh@agliodbs.com> In-Reply-To: <200502090941.01447.josh@agliodbs.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200502/143 X-Sequence-Number: 10472 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)