Re: very slow selects on a small table - Mailing list pgsql-performance

Tom Lane [tgl@sss.pgh.pa.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)