On Feb 10, 2007, at 12:34 PM, Tom Lane wrote:
Arturo Perez <aperez@xxxxxxxxxxxx> 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
