Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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: unknown_filename
Description: text/html (1.5 KB)

In response to

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group