I have a commonly run query that has been executing fine for the last
year or so. It usually completes in under 1 second. However, in the
past week or so the performance of the query has become erratic,
taking anywhere from 100 ms to 100,000 ms to complete the same query
(executed just seconds apart). It's the holiday season, so there has
probably been an increase in server activity.
SELECT products.* FROM products LEFT JOIN product_identifiers ON
product_identifiers.product_id = products.id WHERE
product_identifiers.identifier = '21A40606099800168' OR
products.part_number = '21A40606099800168';
I'm just using this query as a concrete example. I have the same
problem with other queries (I suspect most queries). Here's the
EXPLAIN ANALYZE output run twice. The first time is fast. The second
time is slow.
Then, below that, I've attached some specs from my configuration...
if anyone sees anything that is out of whack... I'm new to
troubleshooting this sort of thing, so any advise would be appreciated.
-------
officelink=# EXPLAIN ANALYZE SELECT products.* FROM products LEFT
JOIN product_identifiers ON product_identifiers.product_id =
products.id WHERE product_identifiers.identifier =
'21A40606099800168' OR products.part_number = '21A40606099800168';
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------
Merge Left Join (cost=0.00..4264.09 rows=40368 width=107) (actual
time=755.150..755.150 rows=0 loops=1)
Merge Cond: ("outer".id = "inner".product_id)
Filter: ((("inner".identifier)::text = '21A40606099800168'::text)
OR (("outer".part_number)::text = '21A40606099800168'::text))
-> Index Scan using products_id_idx on products
(cost=0.00..3680.34 rows=40368 width=107) (actual time=8.762..643.550
rows=40382 loops=1)
-> Index Scan using product_identifiers_product_id_idx on
product_identifiers (cost=0.00..368.69 rows=6524 width=20) (actual
time=0.131..76.958 rows=6532 loops=1)
Total runtime: 755.301 ms
(6 rows)
officelink=# EXPLAIN ANALYZE SELECT products.* FROM products LEFT
JOIN product_identifiers ON product_identifiers.product_id =
products.id WHERE product_identifiers.identifier =
'21A40606099800168' OR products.part_number = '21A40606099800168';
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
--------------------------
Merge Left Join (cost=0.00..4264.09 rows=40368 width=107) (actual
time=25885.235..25885.235 rows=0 loops=1)
Merge Cond: ("outer".id = "inner".product_id)
Filter: ((("inner".identifier)::text = '21A40606099800168'::text)
OR (("outer".part_number)::text = '21A40606099800168'::text))
-> Index Scan using products_id_idx on products
(cost=0.00..3680.34 rows=40368 width=107) (actual
time=0.070..23503.630 rows=40382 loops=1)
-> Index Scan using product_identifiers_product_id_idx on
product_identifiers (cost=0.00..368.69 rows=6524 width=20) (actual
time=0.058..2346.662 rows=6532 loops=1)
Total runtime: 25885.375 ms
(6 rows)
Server Specs:
Intel Core Solo Mac Mini running OS 10.4.7
1.25 GB RAM
30 GB of space left on the 55 GB internal hard drive
Usage:
400 persistent connections from various clients
top usually sits at 85%-95% idle.
postgresql.conf Settings [non-defualt]:
max_connections = 500
shared_buffers = 10000
work_mem = 2048
max_fsm_pages = 150000
max_stack_depth = 6000
archive_command = 'cp -i %p /Volumes/Backup/wal_archive/%f </dev/null'
effective_cache_size = 30000
log_min_duration_statement = 2000
log_line_prefix = '%t %h '
stats_start_collector = on
stats_row_level = on
autovacuum = on
autovacuum_naptime = 60
autovacuum_vacuum_threshold = 150
autovacuum_vacuum_scale_factor = 0.00000001
