From: Oliveiros Cristina <oliveiros.cristina@marktest.pt>
To: pgsql-sql@postgresql.org
Subject: performance question
Date: Fri, 8 May 2009 16:36:34 +0100
Message-ID: <005801c9cff2$c5424660$ec5a3d0a@marktestcr.marktest.pt> (raw)
Hi, All
Suppose you have a query like this
SELECT *
FROM t_sitesresumebydate a
JOIN t_triple_association c
ON "IDSiteResume" = "IDResume"
WHERE "dtDate" BETWEEN '2009-2-1'
AND '2009-2-3'
AND "IDHitsAccount" = 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'
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=46644.50..751271.16 rows=117 width=60) (actual time=15821.110..424120.924 rows=247 loops=1)"
" Hash Cond: (c."IDResume" = a."IDSiteResume")"
" -> Seq Scan on t_triple_association c (cost=0.00..554934.99 rows=29938099 width=32) (actual time=38.253..392251.754 rows=30101626 loops=1)"
" -> Hash (cost=46644.30..46644.30 rows=82 width=28) (actual time=2711.356..2711.356 rows=23 loops=1)"
" -> 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))"
"Total runtime: 424121.180 ms"
"Nested Loop (cost=108.43..745296.34 rows=79 width=60) (actual time=44.283..311.942 rows=185 loops=1)"
" -> 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))"
" -> Bitmap Heap Scan on t_triple_association c (cost=108.43..12658.83 rows=3515 width=32) (actual time=16.902..16.910 rows=11 loops=17)"
" Recheck Cond: (a."IDSiteResume" = c."IDResume")"
" -> 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")"
"Total runtime: 312.192 ms"
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-sql@postgresql.org
Cc: oliveiros.cristina@marktest.pt
Subject: Re: performance question
In-Reply-To: <005801c9cff2$c5424660$ec5a3d0a@marktestcr.marktest.pt>
* 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