Hello all.
To our surprise this morning, we found a query that used to return
it's result in about 50 ~ 100ms now take about 7.000ms to run.
After some investigation, we found out that the PostgreSQL server
(8.1) changed the execution plan (I'm assuming because the number of
rows increased).
Since this query may be executed a few times per second, it caused
some problem :)
The query is the following:
SELECT meta_id, meta_type, COUNT(M.post_id) as count
FROM dc_meta M LEFT JOIN dc_post P ON M.post_id = P.post_id
WHERE P.blog_id = 'b4c62627b3203e7780078cf2f6373ab5'
AND M.blog_id = 'b4c62627b3203e7780078cf2f6373ab5'
AND meta_type = 'tag'
AND ((post_status = 1 AND post_password IS NULL ))
GROUP BY meta_id,meta_type,P.blog_id
ORDER BY count DESC
LIMIT 40
The dc_post table has the following fields:
- post_id bigint NOT NULL,
- blog_id character varying(32) NOT NULL,
- post_password character varying(32),
- post_status smallint NOT NULL DEFAULT 0,
- and some other not used for this query ;)
Usefull indexes:
- dc_pk_post PRIMARY KEY(post_id)
- dc_fk_post_blog FOREIGN KEY (blog_id)
- dc_idx_blog_post_post_status btree (blog_id, post_status)
- dc_idx_post_blog_id btree (blog_id)
dc_meta is as follow:
- meta_id character varying(255) NOT NULL,
- meta_type character varying(64) NOT NULL,
- post_id bigint NOT NULL,
- blog_id character varying(32)
With indexes:
- dc_pk_meta PRIMARY KEY(meta_id, meta_type, post_id)
- dc_fk_meta_blog FOREIGN KEY (blog_id)
- dc_fk_meta_post FOREIGN KEY (post_id)
- dc_idx_meta_blog_id btree (blog_id)
- dc_idx_meta_meta_type btree (meta_type)
- dc_idx_meta_post_id btree (post_id)
(Aren't the foreign keys and index redundant btw? :)
I've attached the EXPLAIN ANALYZE that runs now, the one that runs on
our test server (witch contains data from 10 days ago), and another
one on the production server with nested loop disabled.
The query plan for the test server is the same that the production
server was last week.
On production
dc_meta contains approx 791756 rows
dc_post contains approx 235524 rows
On test :
dc_meta contains approx 641398 rows
dc_post contains approx 211295 rows
The statistics are at the default value everywhere (10)
The 'b4c6' blog is one of your biggest blogs, which contains 9326 tags
and 3178 posts on the production server (9156 / 3132 in test)
Does anyone have and idea why this happened and how we may be able to
fix the problem ?
Disabling nested loop falls back on the previous plan, but we can't
really disable them since the application used (dotclear) and it's db
layer is designed to work with mysql as well.
For the moment I've changed the query to remove the P.blog_id =
'b4c6..' clause and it does the trick, but it's still slower than the
previous one.
Thank you for your time
--
Romuald Brunet
