Db slow down after table partition

Поиск
Список
Период
Сортировка
От AI Rumman
Тема Db slow down after table partition
Дата
Msg-id AANLkTimEAs7gs0LYFcirMx8CDF9WuswbNhKVTxLQafPn@mail.gmail.com
обсуждение исходный текст
Список pgsql-performance
I have more than 1300000 records in crm table and I partioned the table with deleted = 0 key.
It is working fine except that after partioion query is taking more time than the previous one.
I already set constraint_exclusion = on; My DB version is Postgresql 8.1

I added the explain anayze for both the states.
Any idea please why the delay is being occured.

explain analyze
 select *
 from crm as c
 inner join activity as a on c.crmid = a.activityid 
 inner join seactivityrel as s on c.crmid= s.crmid
 where c.deleted = 0;
 
 
 
 Before partiion:
 
  QUERY PLAN  
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join (cost=116378.17..358658.14 rows=8306416340 width=549) (actual time=18273.373..18276.638 rows=1 loops=1)
  Hash Cond: ("outer".crmid = "inner".activityid)
  -> Append (cost=0.00..37825.51 rows=949942 width=329) (actual time=0.051..5753.293 rows=949941 loops=1)
  -> Seq Scan on crm c (cost=0.00..13.25 rows=1 width=280) (actual time=0.002..0.002 rows=0 loops=1)
  Filter: (deleted = 0)
  -> Seq Scan on crm_active c (cost=0.00..37812.26 rows=949941 width=329) (actual time=0.046..3914.645 rows=949941 loops=1)
  Filter: (deleted = 0)
  -> Hash (cost=72725.10..72725.10 rows=1748826 width=153) (actual time=8716.413..8716.413 rows=1 loops=1)
  -> Merge Join (cost=0.00..72725.10 rows=1748826 width=153) (actual time=7122.474..8716.314 rows=1 loops=1)
  Merge Cond: ("outer".activityid = "inner".crmid)
  -> Index Scan using activity_activityid_subject_idx on activity a (cost=0.00..11489.23 rows=343003 width=145) (actual time=0.430..1075.108 rows=343001 loops=1)
  -> Index Scan using seactivityrel_crmid_idx on seactivityrel s (cost=0.00..38518.04 rows=1748826 width=8) (actual time=76.291..5410.545 rows=1748826 loops=1)
 Total runtime: 18276.780 ms
(13 rows)

After partition:

  QUERY PLAN  
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join (cost=115857.19..357283.03 rows=8306416340 width=548) (actual time=85871.145..85874.584 rows=1 loops=1)
  Hash Cond: ("outer".crmid = "inner".activityid)
  -> Append (cost=0.00..37825.51 rows=949942 width=329) (actual time=0.167..72430.097 rows=949941 loops=1)
  -> Seq Scan on crm c (cost=0.00..13.25 rows=1 width=280) (actual time=0.001..0.001 rows=0 loops=1)
  Filter: (deleted = 0)
  -> Seq Scan on crm_active c (cost=0.00..37812.26 rows=949941 width=329) (actual time=0.162..70604.116 rows=949941 loops=1)
  Filter: (deleted = 0)
  -> Hash (cost=73058.13..73058.13 rows=1748826 width=152) (actual time=9603.453..9603.453 rows=1 loops=1)
  -> Merge Join (cost=0.00..73058.13 rows=1748826 width=152) (actual time=7959.707..9603.101 rows=1 loops=1)
  Merge Cond: ("outer".activityid = "inner".crmid)
  -> Index Scan using activity_pkey on activity a (cost=0.00..11822.25 rows=343004 width=144) (actual time=88.467..1167.556 rows=343001 loops=1)
  -> Index Scan using seactivityrel_crmid_idx on seactivityrel s (cost=0.00..38518.04 rows=1748826 width=8) (actual time=0.459..6148.843 rows=1748826 loops=1)
 Total runtime: 85875.591 ms
(13 rows)

В списке pgsql-performance по дате отправления:

Предыдущее
От: Craig Ringer
Дата:
Сообщение: Re: large dataset with write vs read clients
Следующее
От: Neil Whelchel
Дата:
Сообщение: Re: Slow count(*) again...