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

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 (view raw or flat)
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

pgsql-general by date

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

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