Обсуждение: Slower query after psql changed it's execution plan

Список
Период
Сортировка

Slower query after psql changed it's execution plan

От
Romuald Brunet
Дата:
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

Вложения

Re: Slower query after psql changed it's execution plan

От
"Kevin Grittner"
Дата:
Romuald Brunet <> wrote:
> The statistics are at the default value everywhere (10)

Try setting that to 100 and running ANALYZE.

The small size of the sample with the default of 10 happened to land
you with a bad estimate this time.  (If the numbers it has were
actually representative of the data, the plan it's using would be
reasonable.)

-Kevin

Re: Slower query after psql changed it's execution plan

От
Tom Lane
Дата:
Romuald Brunet <> writes:
> 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).

The problem seems to be that the estimate for the number of rows fetched
from dc_post changed drastically --- it was in the right ballpark, and
now it's off by a factor of 30.  Why is that?  Maybe you had had a
special statistics target setting, and it got dropped?

> The statistics are at the default value everywhere (10)

Almost certainly not enough.

            regards, tom lane