Re: Adding TEXT columns tanks performance?
От | Arturo Perez |
---|---|
Тема | Re: Adding TEXT columns tanks performance? |
Дата | |
Msg-id | 435C68EF-E776-4C0F-AFDD-D25E3896F426@hayesinc.com обсуждение исходный текст |
Ответ на | Re: Adding TEXT columns tanks performance? (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-general |
On Feb 10, 2007, at 12:34 PM, Tom Lane wrote: > Arturo Perez <aperez@hayesinc.com> writes: >> Saturday I changed a table to add a varchar(24) and a TEXT column. > > You didn't actually say which of these tables you changed? > >> I'm not very good at reading these but it looks like sort memory >> might >> be too low? > > The runtime seems to be entirely in the index scan on user_tracking. > I'm surprised it doesn't do something to avoid a full-table indexscan > --- in this case, hashing with extended_user as the inner relation > would > seem like the obvious thing. Is user_id a hashable datatype? > > It's possible that adding the columns would have affected the plan by > making it look like a sort or hash would take too much memory, but if > that were it then your hand increase in work_mem should have fixed it. > Tis odd. I don't suppose you know what plan was used before? > > regards, tom lane I did this and now the thing is nicely faster: iht=> alter table user_tracking alter column user_id set statistics 500; ALTER TABLE iht=> analyze user_tracking; ANALYZE iht=> explain analyze SELECT session_id, action, count(ACTION) as hits iht-> FROM extended_user LEFT JOIN user_tracking USING (user_id) iht-> WHERE subscription_id = 1147 iht-> GROUP BY session_id, action iht-> HAVING count(ACTION) > 0; QUERY PLAN ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------- GroupAggregate (cost=125961.69..127082.82 rows=37371 width=60) (actual time=679.115..725.317 rows=7312 loops=1) Filter: (count("action") > 0) -> Sort (cost=125961.69..126055.12 rows=37371 width=60) (actual time=679.067..697.588 rows=16017 loops=1) Sort Key: user_tracking.session_id, user_tracking."action" -> Nested Loop Left Join (cost=5.64..122319.43 rows=37371 width=60) (actual time=0.160..118.177 rows=16017 loops=1) -> Index Scan using extended_user_subscription_id_idx on extended_user (cost=0.00..161.08 rows=134 width=4) (actual time=0.066..1.289 rows=119 loops=1) Index Cond: (subscription_id = 1147) -> Bitmap Heap Scan on user_tracking (cost=5.64..905.77 rows=469 width=64) (actual time=0.162..0.730 rows=135 loops=119) Recheck Cond: ("outer".user_id = user_tracking.user_id) -> Bitmap Index Scan on user_tracking_user_id_idx (cost=0.00..5.64 rows=469 width=0) (actual time=0.139..0.139 rows=135 loops=119) Index Cond: ("outer".user_id = user_tracking.user_id) Total runtime: 732.520 ms (12 rows) thanks all, arturo
В списке pgsql-general по дате отправления: