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:05:58 -0800
Message-ID: <4B43A9B6.30208@ca.com> (raw)
> 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.
cemdb=> SELECT SUM(1) FROM ts_stats_transetgroup_user_weekly b WHERE
ts_interval_start_time >= '2009-12-28' AND ts_interval_start_time <
'2010-01-04';
sum
-------
89758
(1 row)
cemdb=> select sum(1) from ts_stats_transet_user_interval where
ts_interval_start_time >= '2009-01-03' and ts_interval_start_time <
'2009-01-03 08:00';
sum
-----
(1 row)
cemdb=> select sum(1) from ts_stats_transet_user_interval where
ts_interval_start_time >= '2010-01-03' and ts_interval_start_time <
'2010-01-03 08:00';
sum
--------
800000
(1 row)
the estimates in the 1st query plan are OK (since they are the "same").
The 2nd, however, look to be too low. FYI, this query finally completed,
so it wasn't looping but the query plan is very poor:
[24585-cemdb-admin-2010-01-05 10:54:49.511 PST]LOG: duration:
124676746.863 ms execute <unnamed>: 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
[24585-cemdb-admin-2010-01-05 10:54:49.511 PST]DETAIL: parameters: $1 =
'2010-01-03 00:00:00-08', $2 = '2010-01-03 08:00:00-08', $3 =
'2010-01-01 00:00:00-08', $4 = '2010-01-04 00:00:00-08'
compare to:
[root@rdl64xeoserv01 log]# time PGPASSWORD=**** psql -U admin -d cemdb
-c "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 >= '2010-01-03
00:00' and c.ts_interval_start_time < '2010-01-03 08:00' and
b.ts_interval_start_time >= '2009-12-28 00:00' and
b.ts_interval_start_time < '2010-01-04 00:00'"
count
-------
89758
(1 row)
real 0m3.804s
user 0m0.001s
sys 0m0.003s
so why the former ~40,000 times slower?
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: <4B43A9B6.30208@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