Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 297822E005B for ; Tue, 4 Dec 2007 15:44:24 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 07376-01 for ; Tue, 4 Dec 2007 15:44:19 -0400 (AST) Received: from io.link-m.de (io.link-m.de [89.250.128.34]) by postgresql.org (Postfix) with ESMTP id 6CB2F2E2E52 for ; Tue, 4 Dec 2007 15:44:19 -0400 (AST) Received: from cl-40.muc-02.de.sixxs.net (cl-40.muc-02.de.sixxs.net [2001:a60:f000:27::2]) (AUTH: CRAM-MD5 julian@mehnle.net, TLS: TLSv1/SSLv3, 256bits, AES256-SHA) by io.link-m.de with esmtp; Tue, 04 Dec 2007 19:44:16 +0000 id 0008C025.4755AE10.00006179 From: Julian Mehnle To: pgsql-performance@postgresql.org Subject: Bad query plans for queries on partitioned table Date: Tue, 4 Dec 2007 19:44:08 +0000 User-Agent: KMail/1.9.7 MIME-Version: 1.0 Content-Type: multipart/signed; boundary="nextPart2631432.hzStEMn92b"; protocol="application/pgp-signature"; micalg=pgp-sha1 Content-Transfer-Encoding: 7bit Message-Id: <200712041944.13543.julian@mehnle.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/54 X-Sequence-Number: 27870 --nextPart2631432.hzStEMn92b Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi all, I have a large database with e-mail meta-data (no bodies) for over 100 million messages. I am running PostgreSQL 8.2.4 on a server with 2GB of RAM (shared_buffers =3D 240MB, temp_buffers =3D 128MB, work_mem =3D 256MB, maintenance_work_mem =3D 256MB). I have the data split in two separate tables, "email" and "email_extras": Table "public.email" Column | Type | Modifiers -------------------+-----------------------------+----------- id | bigint | not null load_id | integer | not null ts | timestamp without time zone | not null ip | inet | not null mfrom | text | not null helo | text | Table "public.email_extras" Column | Type | Modifiers -------------------+-----------------------------+----------- id | bigint | not null ts | timestamp without time zone | not null size | integer | not null hdr_from | text | Each of these tables has been partitioned equally based on the "ts" (timestamp) field into two dozen or so tables, each covering one week of messages. For testing purposes, I have only one week's partition filled for each of the "email" and "email_extras" tables (email_2007_week34 {,extras}). Now if I perform the following simple join on the "email" and "email_ extras" tables ... SELECT count(*) FROM email INNER JOIN email_extras USING (id, ts) WHERE mfrom <> hdr_from; then I get the following horrendously inefficient plan: QUERY PLAN -------------------------------------------------------------------------= =2D------------------------------------------------ Aggregate (cost=3D391396890.89..391396890.90 rows=3D1 width=3D0) -> Merge Join (cost=3D9338881.64..349156398.02 rows=3D16896197148 wi= dth=3D0) Merge Cond: ((public.email_extras.id =3D public.email.id) AND (p= ublic.email_extras.ts =3D public.email.ts)) Join Filter: (public.email.mfrom <> public.email_extras.hdr_from) -> Sort (cost=3D4592966.95..4658121.33 rows=3D26061752 width= =3D48) Sort Key: public.email_extras.id, public.email_extras.ts -> Append (cost=3D0.00..491969.52 rows=3D26061752 width= =3D48) -> Seq Scan on email_extras (cost=3D0.00..13.30 ro= ws=3D330 width=3D48) -> Seq Scan on email_2007_week13_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week14_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week15_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week16_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week17_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week18_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week19_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week20_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week21_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week22_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week23_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week24_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week25_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week26_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week27_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week28_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week29_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week30_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week31_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week32_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week33_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week34_extras email_extra= s (cost=3D0.00..491597.12 rows=3D26052512 width=3D33) -> Seq Scan on email_2007_week35_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week36_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week37_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week38_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week39_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Seq Scan on email_2007_week40_extras email_extra= s (cost=3D0.00..13.30 rows=3D330 width=3D48) -> Sort (cost=3D4745914.69..4811071.87 rows=3D26062872 width= =3D48) Sort Key: public.email.id, public.email.ts -> Append (cost=3D0.00..644732.72 rows=3D26062872 width= =3D48) -> Seq Scan on email (cost=3D0.00..13.70 rows=3D37= 0 width=3D48) -> Seq Scan on email_2007_week13 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week14 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week15 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week16 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week17 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week18 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week19 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week20 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week21 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week22 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week23 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week24 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week25 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week26 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week27 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week28 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week29 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week30 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week31 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week32 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week33 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week34 email (cost=3D0.0= 0..644349.12 rows=3D26052512 width=3D33) -> Seq Scan on email_2007_week35 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week36 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week37 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week38 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week39 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) -> Seq Scan on email_2007_week40 email (cost=3D0.0= 0..13.70 rows=3D370 width=3D48) (68 rows) However, if I restrict the query to just the partitions that actually do have data in them ... SELECT count(*) FROM email_2007_week34 INNER JOIN email_2007_week34_extras USING (id, ts) WHERE mfrom <> hdr_from; then I get a much better plan that uses a hash join: QUERY PLAN = =20 -------------------------------------------------------------------------= =2D---------------------------------------------------------- Aggregate (cost=3D4266338.94..4266338.95 rows=3D1 width=3D0) -> Hash Join (cost=3D1111362.80..4266336.07 rows=3D1145 width=3D0) Hash Cond: ((email_2007_week34.ts =3D email_2007_week34_extras.t= s) AND (email_2007_week34.id =3D email_2007_week34_extras.id)) Join Filter: (email_2007_week34.mfrom <> email_2007_week34_extra= s.hdr_from) -> Seq Scan on email_2007_week34 (cost=3D0.00..644349.12 rows= =3D26052512 width=3D33) -> Hash (cost=3D491597.12..491597.12 rows=3D26052512 width=3D3= 3) -> Seq Scan on email_2007_week34_extras (cost=3D0.00..49= 1597.12 rows=3D26052512 width=3D33) (7 rows) Yes, I have `ANALYZE`d the database before running the queries. How come the query planner gets thrown off that far by the simple table partitioning? What can I do to put the query planner back on the right track? Julian. --nextPart2631432.hzStEMn92b Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQBHVa4NwL7PKlBZWjsRAgK6AKDpbfJK/rgtviuy5p9am7nyOGevkQCfT3Mp aBjfOlDgsc9L4u4a6vv+8RQ= =p+Iv -----END PGP SIGNATURE----- --nextPart2631432.hzStEMn92b--