pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (7+ messages)

Message-ID: <23864.1070301901@sss.pgh.pa.us>
Permalink:  ../23864.1070301901@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/23864.1070301901@sss.pgh.pa.us

 · 

reply

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