Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id E8282633763 for ; Fri, 8 May 2009 12:51:53 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 23028-10 for ; Fri, 8 May 2009 12:51:51 -0300 (ADT) X-Greylist: delayed 00:15:12.152616 by SQLgrey-1.7.6 Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116]) by mail.postgresql.org (Postfix) with ESMTP id C4B05633098 for ; Fri, 8 May 2009 12:51:51 -0300 (ADT) Received: from suite (suite.marktest.pt [10.61.90.236]) by jupiter.marktest.pt with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id JY270DM4; Fri, 8 May 2009 16:36:35 +0100 Message-ID: <005801c9cff2$c5424660$ec5a3d0a@marktestcr.marktest.pt> From: "Oliveiros Cristina" To: Subject: performance question Date: Fri, 8 May 2009 16:36:34 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0055_01C9CFFB.2666EBF0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200905/32 X-Sequence-Number: 32524 This is a multi-part message in MIME format. ------=_NextPart_000_0055_01C9CFFB.2666EBF0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, All Suppose you have a query like this SELECT * FROM t_sitesresumebydate a JOIN t_triple_association c ON "IDSiteResume" =3D "IDResume" WHERE "dtDate" BETWEEN '2009-2-1' AND '2009-2-3' AND "IDHitsAccount" =3D 378284 With this time interval it completes in less than a second. If I add one day to the condition, i.e., if it becomes BETWEEN = '2009-2-1' AND '2009-2-4'=20 the performance is radically different, it jumps to a staggering 424 = seconds. and the number of records returned is just 117 (against 79, by = the former condition) Frankly, I cannot understand the reason for this, it seems the planner = is taking radically diferent plans on the two conditions (they are = below). I have an index on all the fields used in the query. Can anyone help me in fixing this, please? Thanks in advance for your kind help Best, Oliveiros "Hash Join (cost=3D46644.50..751271.16 rows=3D117 width=3D60) (actual = time=3D15821.110..424120.924 rows=3D247 loops=3D1)" " Hash Cond: (c."IDResume" =3D a."IDSiteResume")" " -> Seq Scan on t_triple_association c (cost=3D0.00..554934.99 = rows=3D29938099 width=3D32) (actual time=3D38.253..392251.754 = rows=3D30101626 loops=3D1)" " -> Hash (cost=3D46644.30..46644.30 rows=3D82 width=3D28) (actual = time=3D2711.356..2711.356 rows=3D23 loops=3D1)" " -> Index Scan using "fki_FGK_SITERESUME_ACCOUNT" on = t_sitesresumebydate a (cost=3D0.00..46644.30 rows=3D82 width=3D28) = (actual time=3D881.146..2711.303 rows=3D23 loops=3D1)" " Index Cond: ("IDHitsAccount" =3D 378284)" " Filter: (("dtDate" >=3D '2009-02-01'::date) AND ("dtDate" = <=3D '2009-02-04'::date))" "Total runtime: 424121.180 ms" "Nested Loop (cost=3D108.43..745296.34 rows=3D79 width=3D60) (actual = time=3D44.283..311.942 rows=3D185 loops=3D1)" " -> Index Scan using "fki_FGK_SITERESUME_ACCOUNT" on = t_sitesresumebydate a (cost=3D0.00..46644.30 rows=3D55 width=3D28) = (actual time=3D5.825..23.828 rows=3D17 loops=3D1)" " Index Cond: ("IDHitsAccount" =3D 378284)" " Filter: (("dtDate" >=3D '2009-02-01'::date) AND ("dtDate" <=3D = '2009-02-03'::date))" " -> Bitmap Heap Scan on t_triple_association c = (cost=3D108.43..12658.83 rows=3D3515 width=3D32) (actual = time=3D16.902..16.910 rows=3D11 loops=3D17)" " Recheck Cond: (a."IDSiteResume" =3D c."IDResume")" " -> Bitmap Index Scan on "index" (cost=3D0.00..108.43 = rows=3D3515 width=3D0) (actual time=3D14.466..14.466 rows=3D11 = loops=3D17)" " Index Cond: (a."IDSiteResume" =3D c."IDResume")" "Total runtime: 312.192 ms" ------=_NextPart_000_0055_01C9CFFB.2666EBF0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi, All
 
 
 
Suppose you have a query like = this
 

SELECT *
FROM = t_sitesresumebydate a
JOIN=20 t_triple_association  c
ON "IDSiteResume" =3D = "IDResume"
WHERE "dtDate"=20 BETWEEN '2009-2-1'
AND '2009-2-3'
AND "IDHitsAccount" =3D=20 378284
 
 
With this time interval it completes in = less than a=20 second.
If I add one day to the condition, = i.e., if it=20 becomes BETWEEN '2009-2-1' AND '2009-2-4'
the performance is radically different, = it jumps to=20 a staggering 424 seconds. and the number of records returned is just 117 = (against 79, by the former condition)
 
Frankly, I cannot understand the reason = for this,=20 it seems the planner is taking radically diferent plans on the two = conditions=20 (they are below).
I have an index on all the fields used = in the=20 query.
 
Can anyone help me in fixing this,=20 please?
 
Thanks in advance for your kind = help
 
Best,
Oliveiros
 
 
"Hash Join  = (cost=3D46644.50..751271.16 rows=3D117=20 width=3D60) (actual time=3D15821.110..424120.924 rows=3D247 = loops=3D1)"
"  Hash=20 Cond: (c."IDResume" =3D a."IDSiteResume")"
"  ->  Seq = Scan on=20 t_triple_association c  (cost=3D0.00..554934.99 rows=3D29938099 = width=3D32)=20 (actual time=3D38.253..392251.754 rows=3D30101626 loops=3D1)"
"  = -> =20 Hash  (cost=3D46644.30..46644.30 rows=3D82 width=3D28) (actual=20 time=3D2711.356..2711.356 rows=3D23=20 loops=3D1)"
"        ->  = Index Scan=20 using "fki_FGK_SITERESUME_ACCOUNT" on t_sitesresumebydate a =20 (cost=3D0.00..46644.30 rows=3D82 width=3D28) (actual = time=3D881.146..2711.303 rows=3D23=20 loops=3D1)"
"         &nb= sp;   =20 Index Cond: ("IDHitsAccount" =3D=20 378284)"
"          =    =20 Filter: (("dtDate" >=3D '2009-02-01'::date) AND ("dtDate" <=3D=20 '2009-02-04'::date))"
"Total runtime: 424121.180 ms"
 
"Nested Loop  = (cost=3D108.43..745296.34 rows=3D79=20 width=3D60) (actual time=3D44.283..311.942 rows=3D185 = loops=3D1)"
"  -> =20 Index Scan using "fki_FGK_SITERESUME_ACCOUNT" on t_sitesresumebydate = a =20 (cost=3D0.00..46644.30 rows=3D55 width=3D28) (actual = time=3D5.825..23.828 rows=3D17=20 loops=3D1)"
"        Index Cond:=20 ("IDHitsAccount" =3D = 378284)"
"       =20 Filter: (("dtDate" >=3D '2009-02-01'::date) AND ("dtDate" <=3D=20 '2009-02-03'::date))"
"  ->  Bitmap Heap Scan on=20 t_triple_association c  (cost=3D108.43..12658.83 rows=3D3515 = width=3D32) (actual=20 time=3D16.902..16.910 rows=3D11=20 loops=3D17)"
"        Recheck = Cond:=20 (a."IDSiteResume" =3D=20 c."IDResume")"
"        = ->  Bitmap=20 Index Scan on "index"  (cost=3D0.00..108.43 rows=3D3515 width=3D0) = (actual=20 time=3D14.466..14.466 rows=3D11=20 loops=3D17)"
"         &n= bsp;   =20 Index Cond: (a."IDSiteResume" =3D c."IDResume")"
"Total runtime: = 312.192=20 ms"
------=_NextPart_000_0055_01C9CFFB.2666EBF0--