Re: very slow selects on a small table

Tom Lane [tgl(at)sss(dot)pgh(dot)pa(dot)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)