Re: Adding TEXT columns tanks performance?

On Feb 10, 2007, at 12:34 PM, Tom Lane wrote:

> Arturo Perez <aperez(at)hayesinc(dot)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