Re: very slow selects on a small table
От | Brian Cox |
---|---|
Тема | Re: very slow selects on a small table |
Дата | |
Msg-id | 4A397B58.3080108@ca.com обсуждение исходный текст |
Ответ на | very slow selects on a small table (Brian Cox <brian.cox@ca.com>) |
Ответы |
Re: very slow selects on a small table
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | 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)
В списке pgsql-performance по дате отправления: