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

Tom Lane [tgl@sss.pgh.pa.us] wrote:
> Yup.  So according to those stats, all ts_id values fall in the range
> 600000000000000001 .. 600000000000250068.  It's no wonder it's not
> expecting to find anything between 0 and 100000.  I think maybe you
> forgot to re-analyze after loading data ... although this being 8.3,
> I'd have expected autovacuum to update the stats at some point ...
yes, this is a concern. I may have to do the vacuum analyze myself or
learn how to make autovacuum run more frequently.
>
> Recommendation: re-ANALYZE, check that the plan changes to something
> with a higher estimate for the number of rows for this table, and then
> abort and restart those processes.  Lord knows how long you'll be
> waiting for them to finish with their current plans :-(
these queries are still running now 27.5 hours later... These queries
are generated by some java code and in putting it into a test program so
I could capture the queries, I failed to get the id range correct --
sorry for wasting your time with bogus data. Below is the EXPLAIN output
from the 4 correct queries. I can't tell which one is being executed by
PID 7397, but the query plans, except the last, do look very similar. In
any event, as I mentioned, all 4 are still running.

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 >
600000000000010000 and a.ts_id < 600000000000020000 order by a.ts_id;

                             QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=138722.75..138734.37 rows=9299 width=779)
    Sort Key: a.ts_id
    ->  Hash IN Join  (cost=131710.94..138416.28 rows=9299 width=779)
          Hash Cond: (a.ts_id = b.ts_id)
          ->  Index Scan using ts_stats_transetgroup_user_daily_pkey on
ts_stats_transetgroup_user_daily a  (cost=0.00..6602.21 rows=9299 width=779)
                Index Cond: ((ts_id > 600000000000010000::bigint) AND
(ts_id < 600000000000020000::bigint))
          ->  Hash  (cost=130113.34..130113.34 rows=255616 width=8)
                ->  Hash Join  (cost=82370.45..130113.34 rows=255616
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..29255.47 rows=229502
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..27674.33 rows=229502 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)
(17 rows)

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 >
600000000000020000 and a.ts_id < 600000000000030000 order by a.ts_id;

                             QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=139430.64..139443.43 rows=10237 width=779)
    Sort Key: a.ts_id
    ->  Hash IN Join  (cost=131710.94..139089.71 rows=10237 width=779)
          Hash Cond: (a.ts_id = b.ts_id)
          ->  Index Scan using ts_stats_transetgroup_user_daily_pkey on
ts_stats_transetgroup_user_daily a  (cost=0.00..7265.23 rows=10237
width=779)
                Index Cond: ((ts_id > 600000000000020000::bigint) AND
(ts_id < 600000000000030000::bigint))
          ->  Hash  (cost=130113.34..130113.34 rows=255616 width=8)
                ->  Hash Join  (cost=82370.45..130113.34 rows=255616
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..29255.47 rows=229502
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..27674.33 rows=229502 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)
(17 rows)

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 >
600000000000030000 and a.ts_id < 600000000000040000 order by a.ts_id;

                             QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=138685.25..138696.81 rows=9247 width=779)
    Sort Key: a.ts_id
    ->  Hash IN Join  (cost=131710.94..138380.68 rows=9247 width=779)
          Hash Cond: (a.ts_id = b.ts_id)
          ->  Index Scan using ts_stats_transetgroup_user_daily_pkey on
ts_stats_transetgroup_user_daily a  (cost=0.00..6567.19 rows=9247 width=779)
                Index Cond: ((ts_id > 600000000000030000::bigint) AND
(ts_id < 600000000000040000::bigint))
          ->  Hash  (cost=130113.34..130113.34 rows=255616 width=8)
                ->  Hash Join  (cost=82370.45..130113.34 rows=255616
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..29255.47 rows=229502
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..27674.33 rows=229502 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)
(17 rows)

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 >
600000000000040000 and a.ts_id < 9223372036854775807 order by a.ts_id;

                          QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Merge IN Join  (cost=141592.81..290873.68 rows=209136 width=779)
    Merge Cond: (a.ts_id = b.ts_id)
    ->  Index Scan using ts_stats_transetgroup_user_daily_pkey on
ts_stats_transetgroup_user_daily a  (cost=0.00..147334.73 rows=209136
width=779)
          Index Cond: ((ts_id > 600000000000040000::bigint) AND (ts_id <
9223372036854775807::bigint))
    ->  Sort  (cost=141592.81..141912.33 rows=255616 width=8)
          Sort Key: b.ts_id
          ->  Hash Join  (cost=82370.45..130113.34 rows=255616 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..29255.47 rows=229502 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..27674.33 rows=229502 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)
(16 rows)