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