From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alexaki Sofia <alexaki@ics.forth.gr>
Cc: pgsql-sql@postgresql.org
Subject: Re: A question about indexes...
Date: Sun, 06 Aug 2000 01:33:54 -0400
Message-ID: <24199.965540034@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.GSO.4.10.10008041300090.27728-100000@sappho.ics.forth.gr>
References: <Pine.GSO.4.10.10008041300090.27728-100000@sappho.ics.forth.gr>
Alexaki Sofia <alexaki@ics.forth.gr> 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
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, alexaki@ics.forth.gr
Subject: Re: A question about indexes...
In-Reply-To: <24199.965540034@sss.pgh.pa.us>
References: <Pine.GSO.4.10.10008041300090.27728-100000@sappho.ics.forth.gr> <24199.965540034@sss.pgh.pa.us>
* 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