Re: Adding TEXT columns tanks performance?

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?

Sorry, I changed extended_user.

>
>> 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?

user_id is an integer;  Here are the table definitions, since this
seems like a problem
that won't go away anytime soon.

                 Table "public.extended_user"
       Column       |            Type             | Modifiers
-------------------+-----------------------------+-----------
create_date       | timestamp without time zone | not null
email             | character varying(99)       |
first_name        | character varying(99)       | not null
last_name         | character varying(99)       | not null
license_agreement | boolean                     | not null
license_date      | timestamp without time zone |
password          | character varying(32)       | not null
subscription_id   | integer                     | not null
user_id           | integer                     | not null
user_name         | character varying(99)       | not null
active            | boolean                     | not null
phone             | character varying(24)       |
title             | text                        |
Indexes:
     "extended_user_pkey" PRIMARY KEY, btree (user_id) CLUSTER
     "user_name_uq" UNIQUE, btree (user_name)
     "extended_user_subscription_id_idx" btree (subscription_id)
Foreign-key constraints:
     "extended_user_subscription_id_fkey" FOREIGN KEY
(subscription_id) REFERENCES subscription(subscription_id) DEFERRABLE
INITIALLY DEFERRED

\d user_tracking
                                                Table
"public.user_tracking"
       Column      |            Type
|                                Modifiers
------------------+-----------------------------
+-----------------------------------------------------------------------
---
action           | character varying(255)      | not null
entry_date       | timestamp without time zone | not null
note             | text                        |
report_id        | integer                     |
session_id       | character varying(255)      | not null
user_id          | integer                     |
user_tracking_id | integer                     | not null default
nextval('user_tracking_user_tracking_id_seq'::regclass)
Indexes:
     "user_tracking_pkey" PRIMARY KEY, btree (user_tracking_id)
     "user_tracking_monthly_idx" btree (date_part('year'::text,
entry_date), date_part('month'::text, entry_date))
     "user_tracking_quarterly_idx" btree (date_part('year'::text,
entry_date), date_part('quarter'::text, entry_date))
     "user_tracking_report_id_idx" btree (report_id)
     "user_tracking_user_id_idx" btree (user_id)
Foreign-key constraints:
     "user_tracking_report_id_fkey" FOREIGN KEY (report_id)
REFERENCES article(article_id) DEFERRABLE INITIALLY DEFERRED
     "user_tracking_user_id_fkey" FOREIGN KEY (user_id) REFERENCES
extended_user(user_id) DEFERRABLE INITIALLY DEFERRED


>
> 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

No, sorry.  Further information:  on disk the user_tracking table is
over 500MB, I can't
increase shared_buffers (currently 20000) because of SHMMAX limits
(for now, scheduled outage
and all that).

Any suggestions on how to improve the situation?

tias,
-arturo