Received: from sss2.sss.pgh.pa.us (root@sss.pgh.pa.us [209.114.166.2]) by hub.org (8.10.1/8.10.1) with ESMTP id e765Y2G98024 for ; Sun, 6 Aug 2000 01:34:03 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id BAA24202; Sun, 6 Aug 2000 01:33:54 -0400 (EDT) To: Alexaki Sofia cc: pgsql-sql@postgresql.org Subject: Re: A question about indexes... In-reply-to: References: Comments: In-reply-to Alexaki Sofia message dated "Fri, 04 Aug 2000 13:02:47 +0300" Date: Sun, 06 Aug 2000 01:33:54 -0400 Message-ID: <24199.965540034@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200008/44 Alexaki Sofia writes: > But as I see from the query plan the indexes are not used, instead > sequential search is done either I define indexes or not. > As you can see below the query plan remains the same. > Is that reasonable??? Shouldn't Postgresql use the indexes in order > to optimize question??? Not necessarily. Since you're just doing a join without restricting the query to a subset of either table, the indexes would only be useful as a means of ordering the inputs to a mergejoin --- and an indexscan over a whole table is *not* particularly fast, because of all the random seeks involved. The plausible plans for this sort of query are basically Merge Join -> Index Scan on t1 -> Index Scan on t2 Merge Join -> Sort -> Seq Scan on t1 -> Sort -> Seq Scan on t2 Hash Join -> Seq Scan on t1 -> Seq Scan on t2 (Postgres also considers mergejoins with indexscan on one side and explicit sort on the other, but for brevity I ignore that possibility.) Any of these might be the best choice depending on number of rows, width of each row, and harder-to-predict factors like how well-ordered the tuples are already. The planner's cost models are evidently predicting that the hash join will be the quickest. You could experiment, if you're interested, by forcing the choice by setting ENABLE_HASHJOIN and ENABLE_SORT on or off, and then comparing the estimated costs shown by EXPLAIN and the actual measured query runtimes. If the estimated-cost ratios are wildly at variance with the real runtimes then you have a legitimate gripe. But your gripe should be that the cost models don't reflect reality, not that Postgres ignores your indexes. regards, tom lane