From: Tom Lane <tgl@sss.pgh.pa.us>
To: carlos.reimer@opendb.com.br
Cc: pgsql-performance@postgresql.org
Subject: Re: Improving select peformance
Date: Thu, 19 Jul 2007 21:31:08 -0400
Message-ID: <15248.1184895068@sss.pgh.pa.us> (raw)
In-Reply-To: <PEEPKDFEHHEMKBBFPOOKMELPFBAA.carlos.reimer@opendb.com.br>
References: <PEEPKDFEHHEMKBBFPOOKMELPFBAA.carlos.reimer@opendb.com.br>
"Carlos H. Reimer" <carlos.reimer@opendb.com.br> writes:
> One of our end users was complaining about a report that was taking too much
> time to execute and I´ve discovered that the following SQL statement was the
> responsible for it.
Here's part of the problem:
> Join Filter: ((gra.codcor)::text =
> ((div.codite)::text || ''::text))
> -> Hash Join (cost=1.11..3888.04 rows=11
> width=146) (actual time=15.560..85.376 rows=414 loops=1)
> Hash Cond: ((gra.codtam)::text =
> ((sub.codite)::text || ''::text))
Why such bizarre join conditions? Why don't you lose the useless
concatenations of empty strings and have just a plain equality
comparison? This technique completely destroys any chance of the
planner making good estimates of the join result sizes (and the bad
estimates it's coming out with are part of the problem).
> -> Nested Loop (cost=0.00..68318.52 rows=647982 width=85)
> (actual time=0.026..3406.170 rows=643739 loops=414)
> -> Seq Scan on td_nat nat (cost=0.00..1.24 rows=1
> width=9) (actual time=0.004..0.014 rows=1 loops=414)
> Filter: (-3::numeric = codtab)
> -> Seq Scan on tt_ive ive (cost=0.00..61837.46
> rows=647982 width=76) (actual time=0.017..1926.983 rows=643739 loops=414)
> Filter: ((sitmov <> 'C'::bpchar) AND
> ('001'::bpchar = codfil))
The other big problem seems to be that it's choosing to do this
unconstrained join first. I'm not sure about the cause of that,
but maybe you need to increase join_collapse_limit. What PG version
is this anyway?
A more general comment, if you are open to schema changes, is that you
should change all the "numeric(n,0)" fields to integer (or possibly
smallint or bigint as needed). Particularly the ones that are used as
join keys, primary keys, foreign keys.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, carlos.reimer@opendb.com.br
Subject: Re: Improving select peformance
In-Reply-To: <15248.1184895068@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox