I am having huge performance problems with a table. Performance deteriorates every day and I have to run REINDEX and ANALYZE on it every day. Âauto vacuum is on. Âyes, I am reading the other thread about count(*) :)
but obviously I'm doing something wrong here
explain analyze select count(*) from fastadder_fastadderstatus;
Aggregate Â(cost=62458.73..62458.74 rows=1 width=0) (actual time=77130.000..77130.000 rows=1 loops=1)
ÂÂ -> ÂSeq Scan on fastadder_fastadderstatus Â(cost=0.00..61701.18 rows=303018 width=0) (actual time=50.000..76930.000 rows=302479 loops=1)
ÂTotal runtime: 77250.000 ms
directly after REINDEX and ANALYZE:
ÂAggregate Â(cost=62348.70..62348.71 rows=1 width=0) (actual time=15830.000..15830.000 rows=1 loops=1)
ÂÂ -> ÂSeq Scan on fastadder_fastadderstatus Â(cost=0.00..61613.16 rows=294216 width=0) (actual time=30.000..15570.000 rows=302479 loops=1)
ÂTotal runtime: 15830.000 ms
still very bad for a 300k row table
a similar table:
explain analyze select count(*) from fastadder_fastadderstatuslog;
ÂAggregate Â(cost=8332.53..8332.54 rows=1 width=0) (actual time=1270.000..1270.000 rows=1 loops=1)
ÂÂ -> ÂSeq Scan on fastadder_fastadderstatuslog Â(cost=0.00..7389.02 rows=377402 width=0) (actual time=0.000..910.000 rows=377033 loops=1)
ÂTotal runtime: 1270.000 ms
It gets updated quite a bit each day, and this is perhaps the problem.
To me it doesn't seem like that many updates
100-500 rows inserted per day
no deletes
10k-50k updates per day
mostly of this sort: Â set priority=1 where id=12345
is it perhaps this that is causing the performance problem ?
I could rework the app to be more efficient and do updates using batches
where id IN (1,2,3,4...)
I assume that means a more efficient index update compared to individual updates.
There is one routine that updates position_in_queue using a lot (too many) update statements.
Is that likely to be the culprit ?
What else can I do to investigate ?
ÂÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Table "public.fastadder_fastadderstatus"
  ÂColumn    |      Type      |                Modifiers                Â
-------------------+--------------------------+------------------------------------------------------------------------
Âid        Â| integer         Â| not null default nextval('fastadder_fastadderstatus_id_seq'::regclass)
Âapt_id      Â| integer         Â| not null
Âservice_id    Â| integer         Â| not null
Âagent_priority  Â| integer         Â| not null
Âpriority     Â| integer         Â| not null
Âlast_validated  Â| timestamp with time zone |Â
Âlast_sent     | timestamp with time zone |Â
Âlast_checked   Â| timestamp with time zone |Â
Âlast_modified   | timestamp with time zone | not null
Ârunning_status  Â| integer         Â|Â
Âvalidation_status | integer         Â|Â
Âposition_in_queue | integer         Â|Â
Âsent       Â| boolean         Â| not null default false
Âbuilt       | boolean         Â| not null default false
Âwebid_suffix   Â| integer         Â|Â
Âbuild_cache    | text           |Â
Indexes:
ÂÂ Â"fastadder_fastadderstatus_pkey" PRIMARY KEY, btree (id)
ÂÂ Â"fastadder_fastadderstatus_apt_id_key" UNIQUE, btree (apt_id, service_id)
ÂÂ Â"fastadder_fastadderstatus_agent_priority" btree (agent_priority)
ÂÂ Â"fastadder_fastadderstatus_apt_id" btree (apt_id)
ÂÂ Â"fastadder_fastadderstatus_built" btree (built)
ÂÂ Â"fastadder_fastadderstatus_last_checked" btree (last_checked)
ÂÂ Â"fastadder_fastadderstatus_last_validated" btree (last_validated)
ÂÂ Â"fastadder_fastadderstatus_position_in_queue" btree (position_in_queue)
ÂÂ Â"fastadder_fastadderstatus_priority" btree (priority)
ÂÂ Â"fastadder_fastadderstatus_running_status" btree (running_status)
ÂÂ Â"fastadder_fastadderstatus_service_id" btree (service_id)
Foreign-key constraints:
ÂÂ Â"fastadder_fastadderstatus_apt_id_fkey" FOREIGN KEY (apt_id) REFERENCES nsproperties_apt(id) DEFERRABLE INITIALLY DEFERRED
ÂÂ Â"fastadder_fastadderstatus_service_id_fkey" FOREIGN KEY (service_id) REFERENCES fastadder_fastadderservice(id) DEFERRABLE INITIALLY DEFERRED
thanks !
