When I run vacuum analyze it fixes the problem but after 1 or 2 days the
problem comes back
Here is the table structure
Column | Type |
Modifiers | Storage | Stats target | Description
------------------+-----------------------------+----------------------------------------------------------------------+---------+--------------+-------------
response_id | integer | not null default
nextval('btdt_responses_response_id_seq'::regclass) | plain | |
registration_id | bigint | not null
| plain | |
btdt_id | integer | not null
| plain | |
response | integer | not null
| plain | |
creation_date | timestamp without time zone | not null default now()
| plain | |
last_update_date | timestamp without time zone | not null default now()
| plain | |
Indexes:
"btdt_responses_pkey" PRIMARY KEY, btree (response_id)
"btdt_responses_u2" UNIQUE, btree (registration_id, btdt_id)
"btdt_responses_n1" btree (btdt_id)
"btdt_responses_n2" btree (btdt_id, response)
"btdt_responses_n4" btree (creation_date)
"btdt_responses_n5" btree (last_update_date)
"btdt_responses_n6" btree (btdt_id, last_update_date)
Foreign-key constraints:
"btdt_responses_btdt_id_fkey" FOREIGN KEY (btdt_id) REFERENCES
btdt_items(btdt_id)
"btdt_responses_fk1" FOREIGN KEY (btdt_id) REFERENCES btdt_items(btdt_id)
Has OIDs: no
Options: autovacuum_enabled=true, autovacuum_vacuum_scale_factor=0.02,
autovacuum_analyze_scale_factor=0.02
Thanks
From: Igor Neyman [mailto:iney...@perceptron.com]
Sent: Friday, June 5, 2015 11:06 AM
To: Sheena, Prabhjot; pgsql-gene...@postgresql.org;
pgsql-performance@postgresql.org
Subject: RE: Query running slow for only one specific id. (Postgres 9.3) version
From:
pgsql-performance-ow...@postgresql.org<mailto:pgsql-performance-ow...@postgresql.org>
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Sheena, Prabhjot
Sent: Friday, June 05, 2015 1:55 PM
To: pgsql-gene...@postgresql.org<mailto:pgsql-gene...@postgresql.org>;
pgsql-performance@postgresql.org<mailto:pgsql-performance@postgresql.org>
Subject: [PERFORM] Query running slow for only one specific id. (Postgres 9.3)
version
Postgresql 9.3 Version
Guys
Here is the issue that I'm facing for couple of weeks now. I have
table (size 7GB)
If I run this query with this specific registration id it is using the wrong
execution plan and takes more than a minute to complete. Total number of rows
for this registration_id is only 414 in this table
explain analyze SELECT max(last_update_date) AS last_update_date FROM
btdt_responses WHERE registration_id = 8718704208 AND response != 4;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=2902.98..2903.01 rows=1 width=0) (actual
time=86910.730..86910.731 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.57..2902.98 rows=1 width=8) (actual
time=86910.725..86910.725 rows=1 loops=1)
-> Index Scan Backward using btdt_responses_n5 on btdt_responses
(cost=0.57..6425932.41 rows=2214 width=8) (actual time=86910.723..86910.723
rows=1 loops=1)
Index Cond: (last_update_date IS NOT NULL)
Filter: ((response <> 4) AND (registration_id =
8718704208::bigint))
Rows Removed by Filter: 52145434
Total runtime: 86910.766 ms
Same query with any other registration id will come back in milli seconds
explain analyze SELECT max(last_update_date) AS last_update_date FROM
btdt_responses WHERE registration_id = 8688546267 AND response != 4;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=529.75..529.78 rows=1 width=8) (actual time=19.723..19.723
rows=1 loops=1)
-> Index Scan using btdt_responses_u2 on btdt_responses (cost=0.57..529.45
rows=119 width=8) (actual time=0.097..19.689 rows=72 loops=1)
Index Cond: (registration_id = 8688546267::bigint)
Filter: (response <> 4)
Rows Removed by Filter: 22
Total runtime: 19.769 ms
Please let me know what I can do to fix this issue.
Thanks
Not enough info.
Table structure? Is registration_id - PK? If not, what is the distribution of
the values for this table?
When was it analyzed last time? M.b. you need to increase statistics target
for this table:
Index Scan Backward using btdt_responses_n5 on btdt_responses
(cost=0.57..6425932.41 rows=2214 width=8) (actual time=86910.723..86910.723
rows=1 loops=1)
It expects 2214 records while really getting only 1.
Regards,
Igor Neyman
