Received: from mail.toppoint.de (LaForge.toppoint.de [195.244.243.6]) by hub.org (8.9.3/8.9.3) with SMTP id VAA55498 for ; Sun, 21 Nov 1999 21:33:15 -0500 (EST) (envelope-from feki.toppoint.de!marten@toppoint.de) Received: by mail.toppoint.de (Smail3.1.29.1) id ; Mon, 22 Nov 99 03:33 MET Received: (from marten@localhost) by feki.toppoint.de (8.9.3/8.9.3) id MAA21246 for pgsql-sql@postgreSQL.org; Sun, 21 Nov 1999 12:12:00 +0100 From: marten@feki.toppoint.de Message-Id: <199911211112.MAA21246@feki.toppoint.de> Subject: How to optimize this simple query :-( To: pgsql-sql@postgreSQL.org Date: Sun, 21 Nov 1999 12:12:00 +0100 (CET) X-Mailer: ELM [version 2.4ME+ PL47 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit I've the follwoing two tables: Table P3AT AO CHAR(15) AT Integer AV VARCHAR(80) AB LargeInteger Table P3RL RELS CHAR(15) RELT CHAR(15) SRCT Integer and some other columns Indices are on: P3AT.AO, P3AT.AT, P3RL.RELS, P3RL.RELT, P3RL.SRCT Now I do the query: a) SELECT AO,AT,AV FROM P3AT WHERE AO IN (SELECT RELT FROM P3RL WHERE RELS='9#####3#####RW#' AND (SRCT=1004025)) or via b) SELECT AO,AT,AV FROM P3AT WHERE EXISTS (SELECT RELT FROM P3RL WHERE (RELT=AO) AND (RELS= ..) AND ...) Both statements have the same explain result: Seq Scan on p3at (cost=14458.84 rows=327480 width=28) SubPlan -> Index Scan using reltrgind om p3rl (cost=2.05 rows=1 width=12) Both statements need about 3300 milliseconds to do the job ... and return the three result rows ... I gave it another sql-query and this works much better: c) SELECT AO,AT,AV FROM P3AT,P3RL WHERE (P3AT.AO=P3RL.RELT) AND (RELS= ...) AND (SRCT= ...) This gives the following explain result (and the one I would have expected): NestedLoop (cost=4.1 rows=5 width=40) -> Index Scan using relsrcind on p3rl (cost=2.05 rows=1 width=12) -> Index Scan using atrownind on p3at (cost=2.05 rows=327480 width=28) and needs only 5 ms ! Marten