From: Brian Cox <brian.cox@ca.com>
To: Robert Haas [robertmhaas@gmail.com] <robertmhaas@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: query looping?
Date: Tue, 05 Jan 2010 13:33:08 -0800
Message-ID: <4B43B014.1090003@ca.com> (raw)
also compare:
[4258-cemdb-admin-2010-01-05 13:11:42.913 PST]LOG: duration: 6401.314
ms statement: execute foo('2010-01-03 00:00','2010-01-03
08:00','2009-12-28 00:00','2010-01-04 00:00');
[4258-cemdb-admin-2010-01-05 13:11:42.913 PST]DETAIL: prepare: prepare
foo as select count(distinct b.ts_id) from
ts_stats_transetgroup_user_weekly b, ts_stats_transet_user_interval c,
ts_transetgroup_transets_map m where b.ts_transet_group_id =
m.ts_transet_group_id and m.ts_transet_incarnation_id =
c.ts_transet_incarnation_id and c.ts_user_incarnation_id =
b.ts_user_incarnation_id and c.ts_interval_start_time >= $1 and
c.ts_interval_start_time < $2 and b.ts_interval_start_time >= $3 and
b.ts_interval_start_time < $4;
still the original query is ~20,000 times slower. Here's the explain foo
output for the execute above:
cemdb=> explain execute foo('2010-01-03 00:00','2010-01-03
08:00','2009-12-28 00:00','2010-01-04 00:00');
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=347318.10..347318.11 rows=1 width=8)
-> Hash Join (cost=3836.14..347317.41 rows=549 width=8)
Hash Cond: ((b.ts_transet_group_id = m.ts_transet_group_id)
AND (c.ts_transet_incarnation_id = m.ts_transet_incarnation_id))
-> Hash Join (cost=3834.30..347302.98 rows=2628 width=24)
Hash Cond: (c.ts_user_incarnation_id =
b.ts_user_incarnation_id)
-> Bitmap Heap Scan on ts_stats_transet_user_interval c
(cost=2199.30..343132.02 rows=103500 width=16)
Recheck Cond: ((ts_interval_start_time >= $1) AND
(ts_interval_start_time < $2))
-> Bitmap Index Scan on
ts_stats_transet_user_interval_starttime (cost=0.00..2186.36
rows=103500 width=0)
Index Cond: ((ts_interval_start_time >= $1)
AND (ts_interval_start_time < $2))
-> Hash (cost=1627.99..1627.99 rows=1122 width=24)
-> Index Scan using
ts_stats_transetgroup_user_weekly_starttimeindex on
ts_stats_transetgroup_user_weekly b (cost=0.00..1627.99 rows=1122 width=24)
Index Cond: ((ts_interval_start_time >= $3)
AND (ts_interval_start_time < $4))
-> Hash (cost=1.33..1.33 rows=67 width=16)
-> Seq Scan on ts_transetgroup_transets_map m
(cost=0.00..1.33 rows=67 width=16)
(14 rows)
comparing this to the 1st explain foo output shows some minor
differences in row estimates -- but nothing, I assume, that could
explain the huge time difference. Of course, the 1st plan may not (and
probably? wasn't) the plan that was used to take 124M ms.
Any thoughts on how to avoid this?
Thanks,
Brian
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-performance@postgresql.org
Cc: brian.cox@ca.com, robertmhaas@gmail.com
Subject: Re: query looping?
In-Reply-To: <4B43B014.1090003@ca.com>
* 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