pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
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: Mon, 04 Jan 2010 14:24:47 -0800
Message-ID: <4B426AAF.20408@ca.com> (raw)

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)



view thread (9+ messages)  latest in thread

Message-ID: <4B426AAF.20408@ca.com>
Permalink:  ../4B426AAF.20408@ca.com/
Also on:    postgresql.org/message-id/4B426AAF.20408@ca.com

 · 

reply
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: <4B426AAF.20408@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