Re: Improve response time of a SQL command

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: carlos(dot)reimer(at)opendb(dot)com(dot)br
Cc: "Pgsql-General(at)Postgresql(dot)Org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Improve response time of a SQL command
Date: 2006-12-28 15:17:59
Message-ID: 7104.1167319079@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Carlos H. Reimer" <carlos(dot)reimer(at)opendb(dot)com(dot)br> writes:
> I would like to improve the response time of the following SQL command

Try getting rid of the silly concatenate-with-empty-string operations
that appear to lurk within some view underlying this command. Those
seem to be defeating the planner's ability to guess how many join rows
there will be.

> -> Nested Loop (cost=4.69..2231.03
> rows=12 width=1264) (actual time=3.775..6353.475 rows=32069 loops=1)
> -> Hash Join (cost=4.69..2081.99
> rows=11 width=658) (actual time=3.257..1305.769 rows=32069 loops=1)
> Hash Cond:
> (("outer".codcor)::text = (("inner".codite)::text || ''::text))

It would surely not have used a nestloop here if it had had a better
idea of how many rows would come out of the lower join ... but since it
has no statistics about the result of the concatenate, it's just
guessing about that.

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Wenjian Yang 2006-12-28 15:18:37 LDAP configuration problem
Previous Message antsumees 2006-12-28 13:39:10 FW: select union with table name