From: Tom Lane <tgl@sss.pgh.pa.us>
To: Roger Ging <rging@paccomsys.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Followup - expression (functional) index use in joins
Date: Mon, 01 Dec 2003 13:05:01 -0500
Message-ID: <23864.1070301901@sss.pgh.pa.us> (raw)
In-Reply-To: <3FCB7ADA.8020101@paccomsys.com>
References: <3FC4F360.2090609@paccomsys.com>
<200311261912.01286.dev@archonet.com>
<3FC51B2C.2030700@paccomsys.com>
<18733.1069891765@sss.pgh.pa.us>
<3FCB7ADA.8020101@paccomsys.com>
Roger Ging <rging@paccomsys.com> writes:
> See results below.
Thanks for the report. It seems the issue is that the estimate for the
number of matching rows is way off (870 vs 8):
> -> Index Scan using idx_program_mri_id_no_program on program p
> (cost=0.00..3400.74 rows=870 width=40) (actual time=0.041..0.127 rows=8
> loops=32)
which discourages the planner from using a nestloop. I'm not sure we
can do much about this in the short term. There's been some discussion
of keeping statistics about the values of functional indexes, which
would allow a better estimate to be made in this situation; but that
won't happen before 7.5 at the earliest.
> Turning enable_hashjoin off made the query run as it had on v7.3. We
> have worked around this by changing the index from a function call to a
> direct index on a new column with the results of the function maintained
> by a trigger. Would there be performance issues from leaving
> enable_hashjoin off, or do you recomend enabling it, and working around
> function calls in indices?
Turning enable_hashjoin off globally would be a *really bad* idea IMHO.
The workaround with a derived column seems okay, though certainly a pain
in the neck. Can you manage to turn off enable_hashjoin just for this
one query? That might be the best short-term workaround.
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, rging@paccomsys.com
Subject: Re: Followup - expression (functional) index use in joins
In-Reply-To: <23864.1070301901@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