Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 25A6663508B for ; Fri, 8 May 2009 15:25:14 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 04661-01-3 for ; Fri, 8 May 2009 15:25:12 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 08B60636BE4 for ; Fri, 8 May 2009 14:55:39 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n48Htakl010869; Fri, 8 May 2009 13:55:37 -0400 (EDT) To: "Oliveiros Cristina" cc: pgsql-sql@postgresql.org Subject: Re: performance question In-reply-to: <005801c9cff2$c5424660$ec5a3d0a@marktestcr.marktest.pt> References: <005801c9cff2$c5424660$ec5a3d0a@marktestcr.marktest.pt> Comments: In-reply-to "Oliveiros Cristina" message dated "Fri, 08 May 2009 16:36:34 +0100" Date: Fri, 08 May 2009 13:55:36 -0400 Message-ID: <10868.1241805336@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200905/34 X-Sequence-Number: 32526 "Oliveiros Cristina" writes: > Frankly, I cannot understand the reason for this, it seems the planner is taking radically diferent plans on the two conditions (they are below). Yup, and you seem to be near the crossover point where it thinks they have equal cost. You need to be fixing the inaccurate cost estimates. The most obvious problem is the bad rowcount estimate here: > " -> Bitmap Index Scan on "index" (cost=0.00..108.43 rows=3515 width=0) (actual time=14.466..14.466 rows=11 loops=17)" > " Index Cond: (a."IDSiteResume" = c."IDResume")" Perhaps increasing the statistics targets for one or both tables would help on that. Another odd thing is that essentially identical indexscans are taking radically different times: > " -> Index Scan using "fki_FGK_SITERESUME_ACCOUNT" on t_sitesresumebydate a (cost=0.00..46644.30 rows=82 width=28) (actual time=881.146..2711.303 rows=23 loops=1)" > " Index Cond: ("IDHitsAccount" = 378284)" > " Filter: (("dtDate" >= '2009-02-01'::date) AND ("dtDate" <= '2009-02-04'::date))" > " -> Index Scan using "fki_FGK_SITERESUME_ACCOUNT" on t_sitesresumebydate a (cost=0.00..46644.30 rows=55 width=28) (actual time=5.825..23.828 rows=17 loops=1)" > " Index Cond: ("IDHitsAccount" = 378284)" > " Filter: (("dtDate" >= '2009-02-01'::date) AND ("dtDate" <= '2009-02-03'::date))" I think probably the second one was fast because the data is already cached, so you're not making an entirely fair comparison. If your expectation is that the database is going to be operating under mostly cached conditions, then you probably ought to adjust the planner cost parameters to reflect that (look at effective_cache_size, and try reducing random_page_cost). regards, tom lane