Re: Adding TEXT columns tanks performance? - Mailing list 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