DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=bZrz4Ec/7JoD3Djpa11PFSnFjvrW2cPIioF/TVxKeQzlkYrz0UlP2V6cjUJMUK26JD
	HFe9msRywXf99dtKMqk3JG8KnopY5UK9DrMShYlmeSErYBRA3884cb2vIKMHJR+IDnAM
	TW5vwh0hdXKlQ4J/OK5rW5u5znMroDyJB2aLk=
MIME-Version: 1.0
Date: Mon, 23 Mar 2009 14:52:44 +0100
Message-ID: <8ae589230903230652n294f456dn6e3ec7a39bb4c0aa@mail.gmail.com>
Subject: Slower query after psql changed it's execution plan
From: Romuald Brunet <romuald.brunet@gmail.com>
To: pgsql-performance@postgresql.org
Content-Type: multipart/mixed; boundary=001485f6d910bc18300465c994b9

--001485f6d910bc18300465c994b9
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

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

--001485f6d910bc18300465c994b9
Content-Type: text/plain; charset=US-ASCII; name="analyze-before.txt"
Content-Disposition: attachment; filename="analyze-before.txt"
Content-Transfer-Encoding: base64
X-Attachment-Id: f_fsn7v0in0

TGltaXQgIChjb3N0PTM0MzgwLjg3Li4zNDM4MC45NyByb3dzPTQwIHdpZHRoPTk0KSAoYWN0dWFs
IHRpbWU9OTYuNjk4Li45Ni43NTMgcm93cz00MCBsb29wcz0xKQogIC0+ICBTb3J0ICAoY29zdD0z
NDM4MC44Ny4uMzQzODEuMTYgcm93cz0xMTUgd2lkdGg9OTQpIChhY3R1YWwgdGltZT05Ni42OTYu
Ljk2LjcxMiByb3dzPTQwIGxvb3BzPTEpCiAgICAgICAgU29ydCBLZXk6IGNvdW50KG0ucG9zdF9p
ZCkKICAgICAgICAtPiAgSGFzaEFnZ3JlZ2F0ZSAgKGNvc3Q9MzQzNzUuNTAuLjM0Mzc2Ljk0IHJv
d3M9MTE1IHdpZHRoPTk0KSAoYWN0dWFsIHRpbWU9ODUuMzIwLi44OS4wNjQgcm93cz0zNjg4IGxv
b3BzPTEpCiAgICAgICAgICAgICAgLT4gIEhhc2ggSm9pbiAgKGNvc3Q9MTAyOTguMjkuLjM0Mzc0
LjM1IHJvd3M9MTE1IHdpZHRoPTk0KSAoYWN0dWFsIHRpbWU9MzUuNzgzLi43MS43MDIgcm93cz04
OTU3IGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAgSGFzaCBDb25kOiAoIm91dGVyIi5wb3N0
X2lkID0gImlubmVyIi5wb3N0X2lkKQogICAgICAgICAgICAgICAgICAgIC0+ICBCaXRtYXAgSGVh
cCBTY2FuIG9uIGRjX21ldGEgbSAgKGNvc3Q9MzM2LjkzLi4yNDM0MS4zMyByb3dzPTk0MDEgd2lk
dGg9NTgpIChhY3R1YWwgdGltZT0zLjgxMi4uMjYuNzIwIHJvd3M9OTE1NiBsb29wcz0xKQogICAg
ICAgICAgICAgICAgICAgICAgICAgIFJlY2hlY2sgQ29uZDogKChibG9nX2lkKTo6dGV4dCA9ICdi
NGM2MjYyN2IzMjAzZTc3ODAwNzhjZjJmNjM3M2FiNSc6OnRleHQpCiAgICAgICAgICAgICAgICAg
ICAgICAgICAgRmlsdGVyOiAoKG1ldGFfdHlwZSk6OnRleHQgPSAndGFnJzo6dGV4dCkKICAgICAg
ICAgICAgICAgICAgICAgICAgICAtPiAgQml0bWFwIEluZGV4IFNjYW4gb24gZGNfaWR4X21ldGFf
YmxvZ19pZCAgKGNvc3Q9MC4wMC4uMzM2LjkzIHJvd3M9OTQwNyB3aWR0aD0wKSAoYWN0dWFsIHRp
bWU9My4xNjguLjMuMTY4IHJvd3M9OTE1NiBsb29wcz0xKQogICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgIEluZGV4IENvbmQ6ICgoYmxvZ19pZCk6OnRleHQgPSAnYjRjNjI2MjdiMzIwM2U3
NzgwMDc4Y2YyZjYzNzNhYjUnOjp0ZXh0KQogICAgICAgICAgICAgICAgICAgIC0+ICBIYXNoICAo
Y29zdD05OTU0LjkyLi45OTU0LjkyIHJvd3M9MjU3NyB3aWR0aD00NCkgKGFjdHVhbCB0aW1lPTMx
LjkxOC4uMzEuOTE4IHJvd3M9MzAzOCBsb29wcz0xKQogICAgICAgICAgICAgICAgICAgICAgICAg
IC0+ICBCaXRtYXAgSGVhcCBTY2FuIG9uIGRjX3Bvc3QgcCAgKGNvc3Q9MzUuNTEuLjk5NTQuOTIg
cm93cz0yNTc3IHdpZHRoPTQ0KSAoYWN0dWFsIHRpbWU9Mi41MDguLjI4Ljc1MSByb3dzPTMwMzgg
bG9vcHM9MSkKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBSZWNoZWNrIENvbmQ6ICgo
KGJsb2dfaWQpOjp0ZXh0ID0gJ2I0YzYyNjI3YjMyMDNlNzc4MDA3OGNmMmY2MzczYWI1Jzo6dGV4
dCkgQU5EIChwb3N0X3N0YXR1cyA9IDEpKQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
IEZpbHRlcjogKHBvc3RfcGFzc3dvcmQgSVMgTlVMTCkKICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAtPiAgQml0bWFwIEluZGV4IFNjYW4gb24gZGNfaWR4X2Jsb2dfcG9zdF9wb3N0X3N0
YXR1cyAgKGNvc3Q9MC4wMC4uMzUuNTEgcm93cz0yNTg0IHdpZHRoPTApIChhY3R1YWwgdGltZT0x
LjY1MC4uMS42NTAgcm93cz0zMDM4IGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgSW5kZXggQ29uZDogKCgoYmxvZ19pZCk6OnRleHQgPSAnYjRjNjI2MjdiMzIw
M2U3NzgwMDc4Y2YyZjYzNzNhYjUnOjp0ZXh0KSBBTkQgKHBvc3Rfc3RhdHVzID0gMSkpClRvdGFs
IHJ1bnRpbWU6IDk3LjE5MSBtcwo=
--001485f6d910bc18300465c994b9
Content-Type: text/plain; charset=US-ASCII; name="analyze-now.txt"
Content-Disposition: attachment; filename="analyze-now.txt"
Content-Transfer-Encoding: base64
X-Attachment-Id: f_fsn7v1lc1

TGltaXQgIChjb3N0PTI3NDAuMDMuLjI3NDAuMDMgcm93cz0xIHdpZHRoPTk2KSAoYWN0dWFsIHRp
bWU9NzcxMi44NDQuLjc3MTIuOTIzIHJvd3M9NDAgbG9vcHM9MSkKICAtPiAgU29ydCAgKGNvc3Q9
Mjc0MC4wMy4uMjc0MC4wMyByb3dzPTEgd2lkdGg9OTYpIChhY3R1YWwgdGltZT03NzEyLjg0MC4u
NzcxMi44NDAgcm93cz00MCBsb29wcz0xKQogICAgICAgIFNvcnQgS2V5OiBjb3VudChtLnBvc3Rf
aWQpCiAgICAgICAgLT4gIEhhc2hBZ2dyZWdhdGUgIChjb3N0PTI3NDAuMDEuLjI3NDAuMDIgcm93
cz0xIHdpZHRoPTk2KSAoYWN0dWFsIHRpbWU9NzcwMC40MDQuLjc3MDQuNDcwIHJvd3M9MzcyNiBs
b29wcz0xKQogICAgICAgICAgICAgIC0+ICBOZXN0ZWQgTG9vcCAgKGNvc3Q9MTMuMTkuLjI3NDAu
MDAgcm93cz0xIHdpZHRoPTk2KSAoYWN0dWFsIHRpbWU9My4zNzkuLjc2ODIuOTEwIHJvd3M9OTEy
MyBsb29wcz0xKQogICAgICAgICAgICAgICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIGRjX2lk
eF9ibG9nX3Bvc3RfcG9zdF9zdGF0dXMgb24gZGNfcG9zdCBwICAoY29zdD0wLjAwLi41OTAuMjgg
cm93cz0xMTggd2lkdGg9NDQpIChhY3R1YWwgdGltZT0wLjE2Ny4uMTcuNzA3IHJvd3M9MzA4MyBs
b29wcz0xKQogICAgICAgICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6ICgoKGJsb2dfaWQp
Ojp0ZXh0ID0gJ2I0YzYyNjI3YjMyMDNlNzc4MDA3OGNmMmY2MzczYWI1Jzo6dGV4dCkgQU5EIChw
b3N0X3N0YXR1cyA9IDEpKQogICAgICAgICAgICAgICAgICAgICAgICAgIEZpbHRlcjogKHBvc3Rf
cGFzc3dvcmQgSVMgTlVMTCkKICAgICAgICAgICAgICAgICAgICAtPiAgQml0bWFwIEhlYXAgU2Nh
biBvbiBkY19tZXRhIG0gIChjb3N0PTEzLjE5Li4xOC4yMSByb3dzPTEgd2lkdGg9NjApIChhY3R1
YWwgdGltZT0yLjQ3Ni4uMi40ODAgcm93cz0zIGxvb3BzPTMwODMpCiAgICAgICAgICAgICAgICAg
ICAgICAgICAgUmVjaGVjayBDb25kOiAoKG0ucG9zdF9pZCA9ICJvdXRlciIucG9zdF9pZCkgQU5E
ICgobS5ibG9nX2lkKTo6dGV4dCA9ICdiNGM2MjYyN2IzMjAzZTc3ODAwNzhjZjJmNjM3M2FiNSc6
OnRleHQpKQogICAgICAgICAgICAgICAgICAgICAgICAgIEZpbHRlcjogKChtZXRhX3R5cGUpOjp0
ZXh0ID0gJ3RhZyc6OnRleHQpCiAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIEJpdG1hcEFu
ZCAgKGNvc3Q9MTMuMTkuLjEzLjE5IHJvd3M9MSB3aWR0aD0wKSAoYWN0dWFsIHRpbWU9Mi40NzIu
LjIuNDcyIHJvd3M9MCBsb29wcz0zMDgzKQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
IC0+ICBCaXRtYXAgSW5kZXggU2NhbiBvbiBkY19pZHhfbWV0YV9wb3N0X2lkICAoY29zdD0wLjAw
Li4zLjAyIHJvd3M9MjkyIHdpZHRoPTApIChhY3R1YWwgdGltZT0wLjA0Mi4uMC4wNDIgcm93cz0z
IGxvb3BzPTMwODMpCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgSW5kZXgg
Q29uZDogKG0ucG9zdF9pZCA9ICJvdXRlciIucG9zdF9pZCkKICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAtPiAgQml0bWFwIEluZGV4IFNjYW4gb24gZGNfaWR4X21ldGFfYmxvZ19pZCAg
KGNvc3Q9MC4wMC4uOS45MiByb3dzPTgzNCB3aWR0aD0wKSAoYWN0dWFsIHRpbWU9Mi43MzcuLjIu
NzM3IHJvd3M9OTMyNiBsb29wcz0yNzM1KQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgIEluZGV4IENvbmQ6ICgoYmxvZ19pZCk6OnRleHQgPSAnYjRjNjI2MjdiMzIwM2U3Nzgw
MDc4Y2YyZjYzNzNhYjUnOjp0ZXh0KQoKVG90YWwgcnVudGltZTogNzcxMy4zOTIgbXMK
--001485f6d910bc18300465c994b9
Content-Type: text/plain; charset=US-ASCII; name="analyze-nonestloop.txt"
Content-Disposition: attachment; filename="analyze-nonestloop.txt"
Content-Transfer-Encoding: base64
X-Attachment-Id: f_fsn7v20p2

TGltaXQgIChjb3N0PTQwMzEuOTIuLjQwMzEuOTIgcm93cz0xIHdpZHRoPTk2KSAoYWN0dWFsIHRp
bWU9NjYuODgxLi42Ni45MzQgcm93cz00MCBsb29wcz0xKQogIC0+ICBTb3J0ICAoY29zdD00MDMx
LjkyLi40MDMxLjkyIHJvd3M9MSB3aWR0aD05NikgKGFjdHVhbCB0aW1lPTY2Ljg3OS4uNjYuOTA1
IHJvd3M9NDAgbG9vcHM9MSkKICAgICAgICBTb3J0IEtleTogY291bnQobS5wb3N0X2lkKQogICAg
ICAgIC0+ICBIYXNoQWdncmVnYXRlICAoY29zdD00MDMxLjkwLi40MDMxLjkxIHJvd3M9MSB3aWR0
aD05NikgKGFjdHVhbCB0aW1lPTU0Ljc4NS4uNTguNjAwIHJvd3M9MzcyNiBsb29wcz0xKQogICAg
ICAgICAgICAgIC0+ICBIYXNoIEpvaW4gIChjb3N0PTU4Mi41Ny4uNDAzMS44OSByb3dzPTEgd2lk
dGg9OTYpIChhY3R1YWwgdGltZT0xNS4wNDcuLjQwLjc3MiByb3dzPTkxMjggbG9vcHM9MSkKICAg
ICAgICAgICAgICAgICAgICBIYXNoIENvbmQ6ICgib3V0ZXIiLnBvc3RfaWQgPSAiaW5uZXIiLnBv
c3RfaWQpCiAgICAgICAgICAgICAgICAgICAgLT4gIEJpdG1hcCBIZWFwIFNjYW4gb24gZGNfbWV0
YSBtICAoY29zdD05LjkyLi4zNDU1LjA2IHJvd3M9ODM0IHdpZHRoPTYwKSAoYWN0dWFsIHRpbWU9
My40MTMuLjE1LjExNiByb3dzPTkzMjYgbG9vcHM9MSkKICAgICAgICAgICAgICAgICAgICAgICAg
ICBSZWNoZWNrIENvbmQ6ICgoYmxvZ19pZCk6OnRleHQgPSAnYjRjNjI2MjdiMzIwM2U3NzgwMDc4
Y2YyZjYzNzNhYjUnOjp0ZXh0KQogICAgICAgICAgICAgICAgICAgICAgICAgIEZpbHRlcjogKCht
ZXRhX3R5cGUpOjp0ZXh0ID0gJ3RhZyc6OnRleHQpCiAgICAgICAgICAgICAgICAgICAgICAgICAg
LT4gIEJpdG1hcCBJbmRleCBTY2FuIG9uIGRjX2lkeF9tZXRhX2Jsb2dfaWQgIChjb3N0PTAuMDAu
LjkuOTIgcm93cz04MzQgd2lkdGg9MCkgKGFjdHVhbCB0aW1lPTIuODI5Li4yLjgyOSByb3dzPTkz
MjYgbG9vcHM9MSkKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBJbmRleCBDb25kOiAo
KGJsb2dfaWQpOjp0ZXh0ID0gJ2I0YzYyNjI3YjMyMDNlNzc4MDA3OGNmMmY2MzczYWI1Jzo6dGV4
dCkKICAgICAgICAgICAgICAgICAgICAtPiAgSGFzaCAgKGNvc3Q9NTcyLjM1Li41NzIuMzUgcm93
cz0xMTggd2lkdGg9NDQpIChhY3R1YWwgdGltZT0xMS41NjcuLjExLjU2NyByb3dzPTMwODQgbG9v
cHM9MSkKICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgQml0bWFwIEhlYXAgU2NhbiBvbiBk
Y19wb3N0IHAgIChjb3N0PTIuNzEuLjU3Mi4zNSByb3dzPTExOCB3aWR0aD00NCkgKGFjdHVhbCB0
aW1lPTIuNDcyLi44LjQ2NiByb3dzPTMwODQgbG9vcHM9MSkKICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICBSZWNoZWNrIENvbmQ6ICgoKGJsb2dfaWQpOjp0ZXh0ID0gJ2I0YzYyNjI3YjMy
MDNlNzc4MDA3OGNmMmY2MzczYWI1Jzo6dGV4dCkgQU5EIChwb3N0X3N0YXR1cyA9IDEpKQogICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgIEZpbHRlcjogKHBvc3RfcGFzc3dvcmQgSVMgTlVM
TCkKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgQml0bWFwIEluZGV4IFNjYW4g
b24gZGNfaWR4X2Jsb2dfcG9zdF9wb3N0X3N0YXR1cyAgKGNvc3Q9MC4wMC4uMi43MSByb3dzPTEx
OCB3aWR0aD0wKSAoYWN0dWFsIHRpbWU9MS41MzMuLjEuNTMzIHJvd3M9MzA4NCBsb29wcz0xKQog
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6ICgoKGJsb2df
aWQpOjp0ZXh0ID0gJ2I0YzYyNjI3YjMyMDNlNzc4MDA3OGNmMmY2MzczYWI1Jzo6dGV4dCkgQU5E
IChwb3N0X3N0YXR1cyA9IDEpKQpUb3RhbCBydW50aW1lOiA2Ny4zMTUgbXMK
--001485f6d910bc18300465c994b9--