Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id A4E0F9FB3FF for ; Thu, 19 Jul 2007 22:31:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 81913-07 for ; Thu, 19 Jul 2007 22:31:05 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id C21189FB3ED for ; Thu, 19 Jul 2007 22:31:10 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l6K1V8CR015249; Thu, 19 Jul 2007 21:31:08 -0400 (EDT) To: carlos.reimer@opendb.com.br cc: pgsql-performance@postgresql.org Subject: Re: Improving select peformance In-reply-to: References: Comments: In-reply-to "Carlos H. Reimer" message dated "Thu, 19 Jul 2007 21:41:27 -0300" Date: Thu, 19 Jul 2007 21:31:08 -0400 Message-ID: <15248.1184895068@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/254 X-Sequence-Number: 25938 "Carlos H. Reimer" 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