Tom Lane [tgl@xxxxxxxxxxxxx] 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)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
