From: Robert Haas <robertmhaas@gmail.com>
To: Brian Cox <brian.cox@ca.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: query looping?
Date: Tue, 5 Jan 2010 10:03:37 -0500
Message-ID: <603c8f071001050703g241a3be8i1da20be5f07e670f@mail.gmail.com> (raw)
In-Reply-To: <4B426AAF.20408@ca.com>
References: <4B426AAF.20408@ca.com>
On Mon, Jan 4, 2010 at 5:24 PM, Brian Cox <brian.cox@ca.com> wrote:
> On 01/04/2010 04:53 PM, Robert Haas [robertmhaas@gmail.com] wrote:
>>
>> PREPARE foo AS <the query, with the $x entries still in there>
>> EXPLAIN EXECUTE foo(<the values>);
>
> Thanks for the response. Results below. Brian
>
> cemdb=> 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;
> PREPARE
>
> 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=325382.51..325382.51 rows=1 width=8)
> -> Hash Join (cost=3486.00..325382.00 rows=406 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=3484.17..325370.84 rows=1944 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=2177.34..322486.61 rows=96473 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..2165.28 rows=96473
> width=0)
> Index Cond: ((ts_interval_start_time >= $1) AND
> (ts_interval_start_time < $2))
> -> Hash (cost=1301.21..1301.21 rows=898 width=24)
> -> Index Scan using
> ts_stats_transetgroup_user_weekly_starttimeindex on
> ts_stats_transetgroup_user_weekly b (cost=0.00..1301.21 rows=898 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)
Hmm. Looks like the same plan.
It's not obvious to me what is wrong. Maybe it would make sense to
start by checking the row count estimates for the different rows in
this plan. For example:
SELECT SUM(1) FROM ts_stats_transetgroup_user_weekly b WHERE
ts_interval_start_time > [value] AND ts_interval_start_time < [value];
...and similarly for the bitmap index scan.
...Robert
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: robertmhaas@gmail.com, brian.cox@ca.com
Subject: Re: query looping?
In-Reply-To: <603c8f071001050703g241a3be8i1da20be5f07e670f@mail.gmail.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