From: Tom Lane <tgl@sss.pgh.pa.us>
To: charavay <c.charavay@ibcp.fr>
Cc: pgsql-performance@postgresql.org
Subject: Re: Indexes performance
Date: Mon, 18 Oct 2004 20:02:03 -0400
Message-ID: <9790.1098144123@sss.pgh.pa.us> (raw)
In-Reply-To: <417383CD.3050802@ibcp.fr>
References: <417383CD.3050802@ibcp.fr>
charavay <c.charavay@ibcp.fr> writes:
> ... So the planner decides to scan 33 000 000 of tuples and we would like to
> force it to scan the table dic (303 000 tuples) and to use
> the index on the integer index to execute the join.
I'm mystified why you think that that will be a superior plan. It still
requires visiting every row of the larger table (I assume that all of
the larger table's rows do join to some row of the smaller table).
All that it accomplishes is to force those visits to occur in a
quasi-random order; which not only loses any chance of kernel read-ahead
optimizations, but very likely causes each page of the table to be read
more than once.
AFAICT the planner made exactly the right choice by picking a hashjoin.
Have you tried comparing its estimates against actual runtimes for the
different plans? (See EXPLAIN ANALYZE.)
Offhand the only way I can think of to force it to do the nestloop the
other way around from what it wants to is to temporarily drop the
index it wants to use. You can do that conveniently like so:
begin;
alter table dic drop constraint dic_pkey;
explain analyze select ...;
rollback;
which of course would be no good for production, but it should at least
serve to destroy your illusions about wanting to do it in production.
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, c.charavay@ibcp.fr
Subject: Re: Indexes performance
In-Reply-To: <9790.1098144123@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