X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 32057D1C968 for ; Mon, 1 Dec 2003 18:05:35 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 49990-09 for ; Mon, 1 Dec 2003 14:05:04 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 1914FD1D9DC for ; Mon, 1 Dec 2003 14:05:03 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hB1I5119023865; Mon, 1 Dec 2003 13:05:01 -0500 (EST) To: Roger Ging Cc: pgsql-performance@postgresql.org Subject: Re: Followup - expression (functional) index use in joins 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> Comments: In-reply-to Roger Ging message dated "Mon, 01 Dec 2003 09:31:06 -0800" Date: Mon, 01 Dec 2003 13:05:01 -0500 Message-ID: <23864.1070301901@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/14 X-Sequence-Number: 4874 Roger Ging 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