Re: query planning different in plpgsql?

From: Waldomiro <waldomiro(at)shx(dot)com(dot)br>
To: "Michal J(dot) Kubski" <michal(dot)kubski(at)cdt(dot)pl>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org
Subject: Re: query planning different in plpgsql?
Date: 2009-10-26 19:56:12
Message-ID: 4AE5FEDC.6050102@shx.com.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Try to force a unique plan, like that:<br>
<br>
SELECT field, field2 ...<br>
FROM table1<br>
WHERE field3 = 'xxx'<br>
AND field4 = 'yyy'<br>
AND field5 = 'zzz'<br>
<br>
so, in that example, I need the planner to use my field4 index, but the
planner insists to use the field5, so I rewrite the query like this:<br>
<br>
SELECT field, field2 ...<br>
FROM table1<br>
WHERE trim(field3) = 'xxx'<br>
AND field4 = 'yyy'<br>
AND trim(field5) = 'zzz'<br>
<br>
I&nbsp; didn&acute;t give any option to the planner, so I get what plan I want.<br>
<br>
Waldomiro<br>
<br>
<br>
Tom Lane escreveu:
<blockquote cite="mid:13574(dot)1256580589(at)sss(dot)pgh(dot)pa(dot)us" type="cite">
<pre wrap="">"Michal J. Kubski" <a class="moz-txt-link-rfc2396E" href="mailto:michal(dot)kubski(at)cdt(dot)pl">&lt;michal(dot)kubski(at)cdt(dot)pl&gt;</a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">[ function that creates a bunch of temporary tables and immediately
joins them ]
</pre>
</blockquote>
<pre wrap=""><!---->
It'd probably be a good idea to insert an ANALYZE on the temp tables
after you fill them. The way you've got this set up, there is no chance
of auto-analyze correcting that oversight for you, so the planner will
be planning the join "blind" without any stats. Good results would only
come by pure luck.

regards, tom lane

</pre>
</blockquote>
<br>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 1.5 KB

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Jesper Krogh 2009-10-26 20:02:57 bitmap heap scan way cheaper than seq scan on the same amount of tuples (fts-search).
Previous Message Tom Lane 2009-10-26 18:09:49 Re: query planning different in plpgsql?