pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDifferent Query plans filtering between dates
2+ messages / 2 participants
[nested] [flat]
* Different Query plans filtering between dates
@ 2007-12-24 12:49 Dani Castaños <danitao.mailists@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Dani Castaños @ 2007-12-24 12:49 UTC (permalink / raw)
To: pgsql-sql
Hi all!
I'm trying to find out a solution for this.
I paste you two queries and their query plans:
SELECT se.enduser_id
, se.enduser_number
, se.points
, se.total_messages
, sm.message_order
, to_char( se.creation_time, 'DD/MM/YYYY HH24:MI:SS' ) as first
, MAX( to_char( s.timestamp_in, 'DD/MM/YYYY HH24:MI:SS' ) ) as last
, s.telecom_operator_id
FROM sequence_enduser se
, sequence_messages sm
, statistics s
, statistics_sequence ss
WHERE se.customer_app_config_id = 36052
AND se.current_message_id = sm.sequence_message_id
AND se.enduser_id = ss.enduser_id
AND ss.statistic_id = s.statistic_id
AND s.telecom_operator_id <> 0
AND s.timestamp_in BETWEEN TO_TIMESTAMP( '20071217 00', 'YYYYMMDD HH24'
) AND TO_TIMESTAMP( '20071224 13', 'YYYYMMDD HH24' )
GROUP BY se.enduser_id, se.enduser_number, se.points, se.total_messages,
sm.message_order, se.creation_time, s.telecom_operator_id
ORDER BY enduser_number ASC
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=31324.84..31513.34 rows=75402 width=56) (actual
time=3274.487..3282.754 rows=20275 loops=1)
Sort Key: se.enduser_number
-> GroupAggregate (cost=22200.32..25216.40 rows=75402 width=56)
(actual time=2487.328..3028.551 rows=20275 loops=1)
-> Sort (cost=22200.32..22388.83 rows=75402 width=56) (actual
time=2487.218..2519.218 rows=75367 loops=1)
Sort Key: se.enduser_id, se.enduser_number, se.points,
se.total_messages, sm.message_order, se.creation_time,
s.telecom_operator_id
-> Hash Join (cost=8697.77..16091.89 rows=75402
width=56) (actual time=538.019..1812.511 rows=75367 loops=1)
Hash Cond: ("outer".enduser_id = "inner".enduser_id)
-> Merge Join (cost=7557.54..13066.61 rows=75402
width=25) (actual time=461.141..1599.325 rows=75367 loops=1)
Merge Cond: ("outer".statistic_id =
"inner"."?column3?")
-> Index Scan using pk_st_statistic_id on
"statistics" s (cost=0.00..3800.23 rows=75373 width=28) (actual
time=0.133..836.972 rows=75367 loops=1)
Filter: ((telecom_operator_id <>
0::numeric) AND (timestamp_in >= to_timestamp('20071217 00'::text,
'YYYYMMDD HH24'::text)) AND (timestamp_in <= to_timestamp('20071224
13'::text, 'YYYYMMDD HH24'::text)))
-> Sort (cost=7557.54..7750.19 rows=77061
width=16) (actual time=460.925..495.810 rows=76940 loops=1)
Sort Key: (ss.statistic_id)::numeric
-> Seq Scan on statistics_sequence ss
(cost=0.00..1302.61 rows=77061 width=16) (actual time=0.014..106.970
rows=77046 loops=1)
-> Hash (cost=1088.38..1088.38 rows=20740
width=39) (actual time=76.854..76.854 rows=20285 loops=1)
-> Hash Join (cost=20.02..1088.38 rows=20740
width=39) (actual time=0.321..55.377 rows=20285 loops=1)
Hash Cond: ("outer".current_message_id =
"inner".sequence_message_id)
-> Seq Scan on sequence_enduser se
(cost=0.00..757.25 rows=20740 width=41) (actual time=0.010..27.174
rows=20285 loops=1)
Filter: (customer_app_config_id =
36052)
-> Hash (cost=19.82..19.82 rows=82
width=6) (actual time=0.303..0.303 rows=101 loops=1)
-> Seq Scan on sequence_messages
sm (cost=0.00..19.82 rows=82 width=6) (actual time=0.102..0.215
rows=101 loops=1)
Total runtime: 3321.379 ms
SELECT se.enduser_id
, se.enduser_number
, se.points
, se.total_messages
, sm.message_order
, to_char( se.creation_time, 'DD/MM/YYYY HH24:MI:SS' ) as first
, MAX( to_char( s.timestamp_in, 'DD/MM/YYYY HH24:MI:SS' ) ) as last
, s.telecom_operator_id
FROM sequence_enduser se
, sequence_messages sm
, statistics s
, statistics_sequence ss
WHERE se.customer_app_config_id = 36052
AND se.current_message_id = sm.sequence_message_id
AND se.enduser_id = ss.enduser_id
AND ss.statistic_id = s.statistic_id
AND s.telecom_operator_id <> 0
AND s.timestamp_in BETWEEN TO_TIMESTAMP( '20071224 00', 'YYYYMMDD HH24'
) AND TO_TIMESTAMP( '20071224 12', 'YYYYMMDD HH24' )
GROUP BY se.enduser_id, se.enduser_number, se.points, se.total_messages,
sm.message_order, se.creation_time, s.telecom_operator_id
ORDER BY enduser_number ASC
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=5832.01..5832.01 rows=1 width=56) (actual
time=205888.361..205888.546 rows=452 loops=1)
Sort Key: se.enduser_number
-> GroupAggregate (cost=5831.96..5832.00 rows=1 width=56) (actual
time=205874.680..205885.162 rows=452 loops=1)
-> Sort (cost=5831.96..5831.96 rows=1 width=56) (actual
time=205874.620..205875.244 rows=1436 loops=1)
Sort Key: se.enduser_id, se.enduser_number, se.points,
se.total_messages, sm.message_order, se.creation_time,
s.telecom_operator_id
-> Nested Loop (cost=0.00..5831.95 rows=1 width=56)
(actual time=529.626..205861.898 rows=1436 loops=1)
-> Nested Loop (cost=0.00..5828.92 rows=1
width=58) (actual time=529.608..205843.305 rows=1436 loops=1)
-> Nested Loop (cost=0.00..5825.72 rows=1
width=25) (actual time=529.571..205816.745 rows=1436 loops=1)
Join Filter:
(("inner".statistic_id)::numeric = "outer".statistic_id)
-> Seq Scan on "statistics" s
(cost=0.00..3367.20 rows=1 width=28) (actual time=389.645..422.342
rows=1436 loops=1)
Filter: ((telecom_operator_id <>
0::numeric) AND (timestamp_in >= to_timestamp('20071224 00'::text,
'YYYYMMDD HH24'::text)) AND (timestamp_in <= to_timestamp('20071224
14'::text, 'YYYYMMDD HH24'::text)))
-> Seq Scan on statistics_sequence ss
(cost=0.00..1302.61 rows=77061 width=16) (actual time=0.003..54.903
rows=77049 loops=1436)
-> Index Scan using pk_seqenduser_enduserid
on sequence_enduser se (cost=0.00..3.18 rows=1 width=41) (actual
time=0.014..0.015 rows=1 loops=1436)
Index Cond: (se.enduser_id =
"outer".enduser_id)
Filter: (customer_app_config_id = 36052)
-> Index Scan using pk_seqmsms_seqmsgid on
sequence_messages sm (cost=0.00..3.01 rows=1 width=6) (actual
time=0.008..0.009 rows=1 loops=1436)
Index Cond: ("outer".current_message_id =
sm.sequence_message_id)
Total runtime: 205889.055 ms
As you can see, the only difference between the two queries, is the date
between i filter. Only when I filter between today, the total runtime
grows up till heaven. I don't know what is happening here... The query
plan changes with that damn Nested Loop on third steps...
Note: IF the query is ... AND s.timestamp_in BETWEEN TO_TIMESTAMP(
'20071223 00', 'YYYYMMDD HH24' ) AND TO_TIMESTAMP( '20071223 12',
'YYYYMMDD HH24' )... (Yesterday) The query plan is like the first one...
The Faster.
Please... I need help!
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Different Query plans filtering between dates
@ 2007-12-24 13:12 Pavel Stehule <pavel.stehule@gmail.com>
parent: Dani Castaños <danitao.mailists@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Pavel Stehule @ 2007-12-24 13:12 UTC (permalink / raw)
To: Dani Castaños <danitao.mailists@gmail.com>; +Cc: pgsql-sql
Hello,
it's known problem of some BETWEEN a AMD a. You can find some other
info in archive I thing.
Please check if low is equal high and then don't use BETWEEN.
if a = b then
select ... where some = a
else
select ... where some between a and b
Regards
Pavel Stehule
On 24/12/2007, Dani Castaños <danitao.mailists@gmail.com> wrote:
> Hi all!
>
> I'm trying to find out a solution for this.
> I paste you two queries and their query plans:
>
> SELECT se.enduser_id
> , se.enduser_number
> , se.points
> , se.total_messages
> , sm.message_order
> , to_char( se.creation_time, 'DD/MM/YYYY HH24:MI:SS' ) as first
> , MAX( to_char( s.timestamp_in, 'DD/MM/YYYY HH24:MI:SS' ) ) as last
> , s.telecom_operator_id
> FROM sequence_enduser se
> , sequence_messages sm
> , statistics s
> , statistics_sequence ss
> WHERE se.customer_app_config_id = 36052
> AND se.current_message_id = sm.sequence_message_id
> AND se.enduser_id = ss.enduser_id
> AND ss.statistic_id = s.statistic_id
> AND s.telecom_operator_id <> 0
> AND s.timestamp_in BETWEEN TO_TIMESTAMP( '20071217 00', 'YYYYMMDD HH24'
> ) AND TO_TIMESTAMP( '20071224 13', 'YYYYMMDD HH24' )
> GROUP BY se.enduser_id, se.enduser_number, se.points, se.total_messages,
> sm.message_order, se.creation_time, s.telecom_operator_id
> ORDER BY enduser_number ASC
>
>
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Sort (cost=31324.84..31513.34 rows=75402 width=56) (actual
> time=3274.487..3282.754 rows=20275 loops=1)
> Sort Key: se.enduser_number
> -> GroupAggregate (cost=22200.32..25216.40 rows=75402 width=56)
> (actual time=2487.328..3028.551 rows=20275 loops=1)
> -> Sort (cost=22200.32..22388.83 rows=75402 width=56) (actual
> time=2487.218..2519.218 rows=75367 loops=1)
> Sort Key: se.enduser_id, se.enduser_number, se.points,
> se.total_messages, sm.message_order, se.creation_time,
> s.telecom_operator_id
> -> Hash Join (cost=8697.77..16091.89 rows=75402
> width=56) (actual time=538.019..1812.511 rows=75367 loops=1)
> Hash Cond: ("outer".enduser_id = "inner".enduser_id)
> -> Merge Join (cost=7557.54..13066.61 rows=75402
> width=25) (actual time=461.141..1599.325 rows=75367 loops=1)
> Merge Cond: ("outer".statistic_id =
> "inner"."?column3?")
> -> Index Scan using pk_st_statistic_id on
> "statistics" s (cost=0.00..3800.23 rows=75373 width=28) (actual
> time=0.133..836.972 rows=75367 loops=1)
> Filter: ((telecom_operator_id <>
> 0::numeric) AND (timestamp_in >= to_timestamp('20071217 00'::text,
> 'YYYYMMDD HH24'::text)) AND (timestamp_in <= to_timestamp('20071224
> 13'::text, 'YYYYMMDD HH24'::text)))
> -> Sort (cost=7557.54..7750.19 rows=77061
> width=16) (actual time=460.925..495.810 rows=76940 loops=1)
> Sort Key: (ss.statistic_id)::numeric
> -> Seq Scan on statistics_sequence ss
> (cost=0.00..1302.61 rows=77061 width=16) (actual time=0.014..106.970
> rows=77046 loops=1)
> -> Hash (cost=1088.38..1088.38 rows=20740
> width=39) (actual time=76.854..76.854 rows=20285 loops=1)
> -> Hash Join (cost=20.02..1088.38 rows=20740
> width=39) (actual time=0.321..55.377 rows=20285 loops=1)
> Hash Cond: ("outer".current_message_id =
> "inner".sequence_message_id)
> -> Seq Scan on sequence_enduser se
> (cost=0.00..757.25 rows=20740 width=41) (actual time=0.010..27.174
> rows=20285 loops=1)
> Filter: (customer_app_config_id =
> 36052)
> -> Hash (cost=19.82..19.82 rows=82
> width=6) (actual time=0.303..0.303 rows=101 loops=1)
> -> Seq Scan on sequence_messages
> sm (cost=0.00..19.82 rows=82 width=6) (actual time=0.102..0.215
> rows=101 loops=1)
> Total runtime: 3321.379 ms
>
>
>
> SELECT se.enduser_id
> , se.enduser_number
> , se.points
> , se.total_messages
> , sm.message_order
> , to_char( se.creation_time, 'DD/MM/YYYY HH24:MI:SS' ) as first
> , MAX( to_char( s.timestamp_in, 'DD/MM/YYYY HH24:MI:SS' ) ) as last
> , s.telecom_operator_id
> FROM sequence_enduser se
> , sequence_messages sm
> , statistics s
> , statistics_sequence ss
> WHERE se.customer_app_config_id = 36052
> AND se.current_message_id = sm.sequence_message_id
> AND se.enduser_id = ss.enduser_id
> AND ss.statistic_id = s.statistic_id
> AND s.telecom_operator_id <> 0
> AND s.timestamp_in BETWEEN TO_TIMESTAMP( '20071224 00', 'YYYYMMDD HH24'
> ) AND TO_TIMESTAMP( '20071224 12', 'YYYYMMDD HH24' )
> GROUP BY se.enduser_id, se.enduser_number, se.points, se.total_messages,
> sm.message_order, se.creation_time, s.telecom_operator_id
> ORDER BY enduser_number ASC
>
>
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Sort (cost=5832.01..5832.01 rows=1 width=56) (actual
> time=205888.361..205888.546 rows=452 loops=1)
> Sort Key: se.enduser_number
> -> GroupAggregate (cost=5831.96..5832.00 rows=1 width=56) (actual
> time=205874.680..205885.162 rows=452 loops=1)
> -> Sort (cost=5831.96..5831.96 rows=1 width=56) (actual
> time=205874.620..205875.244 rows=1436 loops=1)
> Sort Key: se.enduser_id, se.enduser_number, se.points,
> se.total_messages, sm.message_order, se.creation_time,
> s.telecom_operator_id
> -> Nested Loop (cost=0.00..5831.95 rows=1 width=56)
> (actual time=529.626..205861.898 rows=1436 loops=1)
> -> Nested Loop (cost=0.00..5828.92 rows=1
> width=58) (actual time=529.608..205843.305 rows=1436 loops=1)
> -> Nested Loop (cost=0.00..5825.72 rows=1
> width=25) (actual time=529.571..205816.745 rows=1436 loops=1)
> Join Filter:
> (("inner".statistic_id)::numeric = "outer".statistic_id)
> -> Seq Scan on "statistics" s
> (cost=0.00..3367.20 rows=1 width=28) (actual time=389.645..422.342
> rows=1436 loops=1)
> Filter: ((telecom_operator_id <>
> 0::numeric) AND (timestamp_in >= to_timestamp('20071224 00'::text,
> 'YYYYMMDD HH24'::text)) AND (timestamp_in <= to_timestamp('20071224
> 14'::text, 'YYYYMMDD HH24'::text)))
> -> Seq Scan on statistics_sequence ss
> (cost=0.00..1302.61 rows=77061 width=16) (actual time=0.003..54.903
> rows=77049 loops=1436)
> -> Index Scan using pk_seqenduser_enduserid
> on sequence_enduser se (cost=0.00..3.18 rows=1 width=41) (actual
> time=0.014..0.015 rows=1 loops=1436)
> Index Cond: (se.enduser_id =
> "outer".enduser_id)
> Filter: (customer_app_config_id = 36052)
> -> Index Scan using pk_seqmsms_seqmsgid on
> sequence_messages sm (cost=0.00..3.01 rows=1 width=6) (actual
> time=0.008..0.009 rows=1 loops=1436)
> Index Cond: ("outer".current_message_id =
> sm.sequence_message_id)
> Total runtime: 205889.055 ms
>
>
>
> As you can see, the only difference between the two queries, is the date
> between i filter. Only when I filter between today, the total runtime
> grows up till heaven. I don't know what is happening here... The query
> plan changes with that damn Nested Loop on third steps...
>
> Note: IF the query is ... AND s.timestamp_in BETWEEN TO_TIMESTAMP(
> '20071223 00', 'YYYYMMDD HH24' ) AND TO_TIMESTAMP( '20071223 12',
> 'YYYYMMDD HH24' )... (Yesterday) The query plan is like the first one...
> The Faster.
>
> Please... I need help!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-12-24 13:12 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-24 12:49 Different Query plans filtering between dates Dani Castaños <danitao.mailists@gmail.com>
2007-12-24 13:12 ` Pavel Stehule <pavel.stehule@gmail.com>
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