Re: very slow selects on a small table

Tom Lane [tgl(at)sss(dot)pgh(dot)pa(dot)us] wrote:
> Um, are you sure that is the query that PID 7397 is running? It doesn't
> match your previous pg_stat_activity printout, nor do I see anything
> about partitioning by PKs.

Umm, indeed. I had to construct the query by hand and left out the
partition part. Here's the full query. Also, I took the liberty of
reducing the completely expanded column list (shown in part in the
pg_stat_activity printout) in the actual query to "*".

Thanks,
Brian

cemdb=# explain select * from ts_stats_transetgroup_user_daily a where
a.ts_id in (select b.ts_id from ts_stats_transetgroup_user_daily
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 >= '2009-6-16 01:00' and
c.ts_interval_start_time < '2009-6-16 02:00') and a.ts_id > 0 and
a.ts_id < 100000 order by a.ts_id;

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop IN Join (cost=82370.45..128489.59 rows=1 width=779)
Join Filter: (b.ts_id = a.ts_id)
-> Index Scan using ts_stats_transetgroup_user_daily_pkey on
ts_stats_transetgroup_user_daily a (cost=0.00..8.22 rows=1 width=779)
Index Cond: ((ts_id > 0) AND (ts_id < 100000))
-> Hash Join (cost=82370.45..127026.87 rows=232721 width=8)
Hash Cond: ((m.ts_transet_group_id = b.ts_transet_group_id)
AND (c.ts_user_incarnation_id = b.ts_user_incarnation_id))
-> Hash Join (cost=3.32..27507.92 rows=213176 width=16)
Hash Cond: (c.ts_transet_incarnation_id =
m.ts_transet_incarnation_id)
-> Index Scan using
ts_stats_transet_user_interval_starttime on
ts_stats_transet_user_interval c (cost=0.00..26039.02 rows=213176 width=16)
Index Cond: ((ts_interval_start_time >=
'2009-06-16 01:00:00-07'::timestamp with time zone) AND
(ts_interval_start_time < '2009-06-16 02:00:00-07'::timestamp with time
zone))
-> Hash (cost=2.58..2.58 rows=117 width=16)
-> Seq Scan on ts_transetgroup_transets_map m
(cost=0.00..2.58 rows=117 width=16)
-> Hash (cost=80511.26..80511.26 rows=247451 width=24)
-> Seq Scan on ts_stats_transetgroup_user_daily b
(cost=0.00..80511.26 rows=247451 width=24)
(14 rows)