performance question - Mailing list pgsql-sql
| From | Oliveiros Cristina |
|---|---|
| Subject | performance question |
| Date | |
| Msg-id | 005801c9cff2$c5424660$ec5a3d0a@marktestcr.marktest.pt Whole thread Raw |
| Responses |
Re: performance question
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-sql |
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"
" 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"
" -> 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"