Re: Problem with index not being chosen inside PL/PgSQL function...

From: "Weber, Geoffrey M(dot)" <Geoffrey(dot)Weber(at)mcleodusa(dot)com>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Problem with index not being chosen inside PL/PgSQL function...
Date: 2007-12-18 19:17:54
Message-ID: 70E5EDFC9C7458478029E57C47FC0B830E59500A0B@MAILCLUSTER1.mcld.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

OK - in that same vain, I thought of something even better - using dynamic SQL instead. It sped things up right away!

Thanks for putting me on the right track!

________________________________________
From: Scott Marlowe [scott(dot)marlowe(at)gmail(dot)com]
Sent: Tuesday, December 18, 2007 12:02 PM
To: Weber, Geoffrey M.
Cc: Tom Lane; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Problem with index not being chosen inside PL/PgSQL function...

On Dec 18, 2007 10:54 AM, Weber, Geoffrey M.
<Geoffrey(dot)Weber(at)mcleodusa(dot)com> wrote:
> Tom,
>
> Yes, the distribution must be what's doing it. I guess I knew that subconciously, but was looking for something like hints to force the planner to do what I wanted. Instead it looks like I'll have to do a bit of tweaking with my indexes. Probably a partial index on the 'not_displayed_id' column. It'll be very small and shouldn't cause much overhead. I was trying to keep my index count down, and have had a dual-column index on (replaced_by_id, not_displayed_id) to this point.

Fix not with a hammer that which you can fix with a screwdriver. Fix
not with a screwdriver that which you can fix with a knob

Have you tried increasing the stats target of the guilty column and
reanalyzing to see if that helps?

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________

NOTICE: This electronic mail transmission may contain confidential information and is intended only for the person(s) named. Any use, copying or disclosure by any other person is strictly prohibited. If you have received this transmission in error, please notify the sender via e-mail.

NOTICE: This electronic mail transmission may contain confidential information and is intended only for the person(s) named. Any use, copying, or disclosure by any other person is strictly prohibited. If you have received this transmission in error, please notify the sender via e-mail.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message hjenkins 2007-12-18 19:44:26 combining semi-duplicate rows
Previous Message Benoît Carpentier 2007-12-18 18:32:03 free ETL tool using postgreSQL, new major release