Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id EEA3F9FB208 for ; Sat, 28 Oct 2006 19:26:58 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 87508-10 for ; Sat, 28 Oct 2006 22:26:50 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from heaven.kostyrka.org (h1634.serverkompetenz.net [81.169.154.44]) by postgresql.org (Postfix) with ESMTP id BFA839FA1A1 for ; Sat, 28 Oct 2006 19:26:49 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by heaven.kostyrka.org (Postfix) with ESMTP id 4ED464FB8E for ; Sun, 29 Oct 2006 00:26:45 +0200 (CEST) Received: from heaven.kostyrka.org ([127.0.0.1]) by localhost (heaven.kostyrka.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 13572-04 for ; Sun, 29 Oct 2006 00:26:45 +0200 (CEST) Received: from [192.168.1.9] (85-124-82-106.dynamic.xdsl-line.inode.at [85.124.82.106]) by heaven.kostyrka.org (Postfix) with ESMTP id C20794FB66 for ; Sun, 29 Oct 2006 00:26:44 +0200 (CEST) Subject: partitioned table performance From: Andreas Kostyrka To: pgsql-performance@postgresql.org Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-3R/GIZlo8I+ZDMK+KlhZ" Date: Sun, 29 Oct 2006 00:28:05 +0200 Message-Id: <1162074485.16385.93.camel@andi-lap> Mime-Version: 1.0 X-Mailer: Evolution 2.6.3 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.135 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200610/599 X-Sequence-Number: 21728 --=-3R/GIZlo8I+ZDMK+KlhZ Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Hi! I'm just wondering, I've got a table that is partitioned into monthly tables: media_downloads -> media_downloads_YYYYMM I\- id (primary key) \- created_on (timestamp criteria for the monthly table split) There are constraints upon the created_on column, all needed insert instead rules are defined too. One additional hardship is that id are not monotone against created_on, id1 < id2 does not imply created_on1 <=3D created_on2 :( The table contains basically almost 100M rows, and the number is growing. (the table will be about a 12GB pg_dump.) All relevant indexes (primary key id, index on created_on) are defined too. The good thing is, queries like all rows in the last 7 days work reasonable fast, the optimizer just checks the 1-2 last month tables. Using postgres 8.1.4-0ubuntu1, I've got to implement the following queries in a reasonable fast way: -- sequential reading of rows SELECT * FROM media_downloads WHERE id > 1000000 ORDER BY id LIMIT 100; Against the same monolithic table with about 16.5M rows, I'm getting a cost of 20.6 pages. (Index scan) Against the partitioned tables, I'm getting a cost of 5406822 pages. Now I understand, that without any additional conditions, postgresql needs to do the query for all subtables first, but explain against the subtables show costs of 4-5 pages. events=3D# explain select * from media_downloads where id >90000000 order by id limit 100; QUERY PLAN = =20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------- Limit (cost=3D5406822.39..5406822.64 rows=3D100 width=3D1764) -> Sort (cost=3D5406822.39..5413639.50 rows=3D2726843 width=3D1764) Sort Key: public.media_downloads.id -> Result (cost=3D0.00..115960.71 rows=3D2726843 width=3D1764) -> Append (cost=3D0.00..115960.71 rows=3D2726843 width=3D1764) -> Seq Scan on media_downloads (cost=3D0.00..10.50 rows=3D13 width=3D1764) Filter: (id > 90000000) -> Index Scan using media_downloads_200510_pkey on media_downloads_200510 media_downloads (cost=3D0.00..3.75 rows=3D14 width=3D243) Index Cond: (id > 90000000) -> Index Scan using media_downloads_200511_pkey on media_downloads_200511 media_downloads (cost=3D0.00..72.19 rows=3D172 width=3D239) Index Cond: (id > 90000000) -> Index Scan using media_downloads_200512_pkey on media_downloads_200512 media_downloads (cost=3D0.00..603.64 rows=3D172 width=3D240) Index Cond: (id > 90000000) -> Index Scan using media_downloads_200601_pkey on media_downloads_200601 media_downloads (cost=3D0.00..19.33 rows=3D232 width=3D239) Index Cond: (id > 90000000) -> Index Scan using media_downloads_200602_pkey on media_downloads_200602 media_downloads (cost=3D0.00..56.82 rows=3D316 width=3D240) Index Cond: (id > 90000000) -> Index Scan using media_downloads_200603_pkey on media_downloads_200603 media_downloads (cost=3D0.00..18.88 rows=3D270 width=3D243) Index Cond: (id > 90000000) -> Index Scan using media_downloads_200604_pkey on media_downloads_200604 media_downloads (cost=3D0.00..1194.16 rows=3D939 width=3D298) Index Cond: (id > 90000000) -> Index Scan using media_downloads_200605_pkey on media_downloads_200605 media_downloads (cost=3D0.00..79.28 rows=3D672 width=3D326) Index Cond: (id > 90000000) -> Index Scan using media_downloads_200606_pkey on media_downloads_200606 media_downloads (cost=3D0.00..75.26 rows=3D1190 width=3D314) Index Cond: (id > 90000000) -> Index Scan using media_downloads_200607_pkey on media_downloads_200607 media_downloads (cost=3D0.00..55.29 rows=3D1238 width=3D319) Index Cond: (id > 90000000) -> Index Scan using media_downloads_200608_pkey on media_downloads_200608 media_downloads (cost=3D0.00..73.95 rows=3D1305 width=3D319) Index Cond: (id > 90000000) -> Index Scan using media_downloads_200609_pkey on media_downloads_200609 media_downloads (cost=3D0.00..144.10 rows=3D1575 width=3D324) Index Cond: (id > 90000000) -> Index Scan using media_downloads_200610_pkey on media_downloads_200610 media_downloads (cost=3D0.00..113532.57 rows=3D2718709 width=3D337) Index Cond: (id > 90000000) -> Seq Scan on media_downloads_200611 media_downloads (cost=3D0.00..10.50 rows=3D13 width=3D1764) Filter: (id > 90000000) -> Seq Scan on media_downloads_200612 media_downloads (cost=3D0.00..10.50 rows=3D13 width=3D1764) Filter: (id > 90000000) (37 rows) events=3D# explain select * from media_downloads_200610 where id >90000000 order by id limit 100; QUERY PLAN =20 ---------------------------------------------------------------------------= -------------------------------------------------- Limit (cost=3D0.00..4.18 rows=3D100 width=3D337) -> Index Scan using media_downloads_200610_pkey on media_downloads_200610 (cost=3D0.00..113582.70 rows=3D2719904 width=3D337) Index Cond: (id > 90000000) (3 rows) Interestingly, if one reformulates the query like that: SELECT * FROM media_downloads WHERE id > 90000000 AND id < 90001000 ORDER BY id LIMIT 100; results in a reasonable cost of 161.5 pages. Now the above query is basically acceptable, as one iterate all rows this way, but now I need to know max(id) to know when to stop my loop: events=3D# explain select max(id) from media_downloads; QUERY PLAN =20 ---------------------------------------------------------------------------= ----------------------------------- Aggregate (cost=3D3676914.56..3676914.58 rows=3D1 width=3D4) -> Append (cost=3D0.00..3444211.85 rows=3D93081085 width=3D4) -> Seq Scan on media_downloads (cost=3D0.00..10.40 rows=3D40 width=3D4) -> Seq Scan on media_downloads_200510 media_downloads (cost=3D0.00..5615.84 rows=3D139884 width=3D4) -> Seq Scan on media_downloads_200511 media_downloads (cost=3D0.00..67446.56 rows=3D1724356 width=3D4) -> Seq Scan on media_downloads_200512 media_downloads (cost=3D0.00..66727.02 rows=3D1718302 width=3D4) -> Seq Scan on media_downloads_200601 media_downloads (cost=3D0.00..88799.91 rows=3D2321991 width=3D4) -> Seq Scan on media_downloads_200602 media_downloads (cost=3D0.00..121525.71 rows=3D3159571 width=3D4) -> Seq Scan on media_downloads_200603 media_downloads (cost=3D0.00..104205.40 rows=3D2701240 width=3D4) -> Seq Scan on media_downloads_200604 media_downloads (cost=3D0.00..342511.42 rows=3D9391242 width=3D4) -> Seq Scan on media_downloads_200605 media_downloads (cost=3D0.00..245167.39 rows=3D6724039 width=3D4) -> Seq Scan on media_downloads_200606 media_downloads (cost=3D0.00..430186.99 rows=3D11901499 width=3D4) -> Seq Scan on media_downloads_200607 media_downloads (cost=3D0.00..451313.72 rows=3D12380172 width=3D4) -> Seq Scan on media_downloads_200608 media_downloads (cost=3D0.00..474743.72 rows=3D13048372 width=3D4) -> Seq Scan on media_downloads_200609 media_downloads (cost=3D0.00..619711.52 rows=3D15754452 width=3D4) -> Seq Scan on media_downloads_200610 media_downloads (cost=3D0.00..426225.45 rows=3D12115845 width=3D4) -> Seq Scan on media_downloads_200611 media_downloads (cost=3D0.00..10.40 rows=3D40 width=3D4) -> Seq Scan on media_downloads_200612 media_downloads (cost=3D0.00..10.40 rows=3D40 width=3D4) (18 rows) events=3D# explain select max(id) from media_downloads_200610; QUERY PLAN =20 ---------------------------------------------------------------------------= ------------------------------------------------------------------ Result (cost=3D0.04..0.05 rows=3D1 width=3D0) InitPlan -> Limit (cost=3D0.00..0.04 rows=3D1 width=3D4) -> Index Scan Backward using media_downloads_200610_pkey on media_downloads_200610 (cost=3D0.00..475660.29 rows=3D12115845 width=3D4) Filter: (id IS NOT NULL) (5 rows) For me as a human, it's obvious, that max(media_downloads) =3D=3D max(media_downloads_200612..media_downloads_200510). Any ideas how to make the optimizer handle partitioned tables more sensible? Andreas --=-3R/GIZlo8I+ZDMK+KlhZ Content-Type: application/pgp-signature; name=signature.asc Content-Description: Dies ist ein digital signierter Nachrichtenteil -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (GNU/Linux) iD8DBQBFQ9l1HJdudm4KnO0RAicMAJ9mHB7qGQSw+fFddEHBLHaca+SWQgCfVgwg N2Zdsh3MIrjBcjlYAXQ9xr8= =7SaC -----END PGP SIGNATURE----- --=-3R/GIZlo8I+ZDMK+KlhZ--