Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 72EEB9FA417 for ; Sat, 27 Oct 2007 05:53:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 99572-06 for ; Sat, 27 Oct 2007 05:53:33 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.177]) by postgresql.org (Postfix) with ESMTP id 67B979FA1A3 for ; Sat, 27 Oct 2007 05:53:31 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id m38so1548664waf for ; Sat, 27 Oct 2007 01:53:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=PLRKqoI+764hAkaEeIvBbQC7jOhB1uAO9brwMONRgRA=; b=lOcB7kSbYWTkIcv/sngp9jxjGzBW5sYFn/fN5kkvt7Mmm8GkxSTfA08cdgFoMqBOP6hM0y7vNwzJxKNArJvGLCqt5woKTwFibyn/Xn9n2yIKu+h0jpaxroZB97ThVFrQ/TlNH4EjWwEQnXG3PJNByepMXgE1QlhGlB1I5z8oETU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=D8K/w4TGOFFXirU9bCGHuKCqRlNToecETEB0GIP4Pr4uwPkEL5pwo6nmqIOjHBJ0/dMfwUa3YmdwkYNH3FRIqsFvxbwN8VP729xizXOwm1pjrSk59TBVDNK27sMzQN45xDRKQG59vYAdyZeo4UfByv+8mlX/vexueBcloS94hK0= Received: by 10.114.80.4 with SMTP id d4mr4465638wab.1193475210459; Sat, 27 Oct 2007 01:53:30 -0700 (PDT) Received: by 10.115.60.17 with HTTP; Sat, 27 Oct 2007 01:53:30 -0700 (PDT) Message-ID: <8cac8dd0710270153v5d6a6dfar1850597750bf17e2@mail.gmail.com> Date: Sat, 27 Oct 2007 14:53:30 +0600 From: Anton To: pgsql-performance@postgresql.org Subject: Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1 In-Reply-To: <8491.1193463449@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <46CEB522.9030907@enterprisedb.com> <8cac8dd0710262126t147f8974rb58a1e7c615e0725@mail.gmail.com> <8491.1193463449@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/319 X-Sequence-Number: 27341 2007/10/27, Tom Lane : > Anton writes: > > I want ask about problem with partioned tables (it was discussed some > > time ago, see below). Is it fixed somehow in 8.2.5 ? > > No. The patch you mention never was considered at all, since it > consisted of a selective quote from Greenplum source code. It would ... > As to whether it would work if we had the full story ... well, not > having the full story, I don't want to opine. Sorry, my english is not good enough to understand your last sentence. I repost here my original question "Why it no uses indexes?" (on partitioned table and ORDER BY indexed_field DESC LIMIT 1), if you mean that you miss this discussion. > I just created partitioned table, n_traf, sliced by month > (n_traf_y2007m01, n_traf_y2007m02... and so on, see below). They are > indexed by 'date_time' column. > Then I populate it (last value have date 2007-08-...) and do VACUUM > ANALYZE ON n_traf_y2007... all of it. > > Now I try to select latest value (ORDER BY date_time LIMIT 1), but > Postgres produced the ugly plan: > > =# explain SELECT * FROM n_traf ORDER BY date_time DESC LIMIT 1; > QUERY PLAN > --------------------------------------------------------------------------------------------------------- > Limit (cost=824637.69..824637.69 rows=1 width=32) > -> Sort (cost=824637.69..838746.44 rows=5643499 width=32) > Sort Key: public.n_traf.date_time > -> Result (cost=0.00..100877.99 rows=5643499 width=32) > -> Append (cost=0.00..100877.99 rows=5643499 width=32) > -> Seq Scan on n_traf (cost=0.00..22.30 > rows=1230 width=32) > -> Seq Scan on n_traf_y2007m01 n_traf > (cost=0.00..22.30 rows=1230 width=32) > -> Seq Scan on n_traf_y2007m02 n_traf > (cost=0.00..22.30 rows=1230 width=32) > -> Seq Scan on n_traf_y2007m03 n_traf > (cost=0.00..22.30 rows=1230 width=32) > -> Seq Scan on n_traf_y2007m04 n_traf > (cost=0.00..1.01 rows=1 width=32) > -> Seq Scan on n_traf_y2007m05 n_traf > (cost=0.00..9110.89 rows=509689 width=32) > -> Seq Scan on n_traf_y2007m06 n_traf > (cost=0.00..32003.89 rows=1790489 width=32) > -> Seq Scan on n_traf_y2007m07 n_traf > (cost=0.00..33881.10 rows=1895510 width=32) > -> Seq Scan on n_traf_y2007m08 n_traf > (cost=0.00..25702.70 rows=1437970 width=32) > -> Seq Scan on n_traf_y2007m09 n_traf > (cost=0.00..22.30 rows=1230 width=32) > -> Seq Scan on n_traf_y2007m10 n_traf > (cost=0.00..22.30 rows=1230 width=32) > -> Seq Scan on n_traf_y2007m11 n_traf > (cost=0.00..22.30 rows=1230 width=32) > -> Seq Scan on n_traf_y2007m12 n_traf > (cost=0.00..22.30 rows=1230 width=32) > (18 rows) > > > Why it no uses indexes at all? > ------------------------------------------- > > The simplier query goes fast, use index. > =# explain analyze SELECT * FROM n_traf_y2007m08 ORDER BY date_time > DESC LIMIT 1; > > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ > Limit (cost=0.00..0.03 rows=1 width=32) (actual time=0.156..0.158 > rows=1 loops=1) > -> Index Scan Backward using n_traf_y2007m08_date_time_login_id on > n_traf_y2007m08 (cost=0.00..39489.48 rows=1437970 width=32) (actual > time=0.150..0.150 rows=1 loops=1) > Total runtime: 0.241 ms > (3 rows) > > Table n_traf looks like this: > =# \d n_traf > Table "public.n_traf" > Column | Type | Modifiers > -------------+-----------------------------+-------------------- > login_id | integer | not null > traftype_id | integer | not null > date_time | timestamp without time zone | not null > bytes_in | bigint | not null default 0 > bytes_out | bigint | not null default 0 > Indexes: > "n_traf_login_id_key" UNIQUE, btree (login_id, traftype_id, date_time) > "n_traf_date_time_login_id" btree (date_time, login_id) > Foreign-key constraints: > "n_traf_login_id_fkey" FOREIGN KEY (login_id) REFERENCES > n_logins(login_id) ON UPDATE CASCADE ON DELETE CASCADE > "n_traf_traftype_id_fkey" FOREIGN KEY (traftype_id) REFERENCES > n_traftypes(traftype_id) ON UPDATE CASCADE > Rules: > n_traf_insert_y2007m01 AS > ON INSERT TO n_traf > WHERE new.date_time >= '2007-01-01'::date AND new.date_time < > '2007-02-01 00:00:00'::timestamp without time zone DO INSTEAD > INSERT INTO n_traf_y2007m01 (login_id, traftype_id, date_time, > bytes_in, bytes_out) > VALUES (new.login_id, new.traftype_id, new.date_time, new.bytes_in, > new.bytes_out) > n_traf_insert_y2007m02 AS > ON INSERT TO n_traf > WHERE new.date_time >= '2007-02-01'::date AND new.date_time < > '2007-03-01 00:00:00'::timestamp without time zone DO INSTEAD > INSERT INTO n_traf_y2007m02 (login_id, traftype_id, date_time, > bytes_in, bytes_out) > VALUES (new.login_id, new.traftype_id, new.date_time, new.bytes_in, > new.bytes_out) > n_traf_insert_y2007m03 AS > ON INSERT TO n_traf > WHERE new.date_time >= '2007-03-01'::date AND new.date_time < > '2007-04-01 00:00:00'::timestamp without time zone DO INSTEAD > INSERT INTO n_traf_y2007m03 (login_id, traftype_id, date_time, > bytes_in, bytes_out) > VALUES (new.login_id, new.traftype_id, new.date_time, new.bytes_in, > new.bytes_out) > n_traf_insert_y2007m04 AS > ON INSERT TO n_traf > WHERE new.date_time >= '2007-04-01'::date AND new.date_time < > '2007-05-01 00:00:00'::timestamp without time zone DO INSTEAD > INSERT INTO n_traf_y2007m04 (login_id, traftype_id, date_time, > bytes_in, bytes_out) > VALUES (new.login_id, new.traftype_id, new.date_time, new.bytes_in, > new.bytes_out) > n_traf_insert_y2007m05 AS > ON INSERT TO n_traf > WHERE new.date_time >= '2007-05-01'::date AND new.date_time < > '2007-06-01 00:00:00'::timestamp without time zone DO INSTEAD > INSERT INTO n_traf_y2007m05 (login_id, traftype_id, date_time, > bytes_in, bytes_out) > VALUES (new.login_id, new.traftype_id, new.date_time, new.bytes_in, > new.bytes_out) > n_traf_insert_y2007m06 AS > ON INSERT TO n_traf > WHERE new.date_time >= '2007-06-01'::date AND new.date_time < > '2007-07-01 00:00:00'::timestamp without time zone DO INSTEAD > INSERT INTO n_traf_y2007m06 (login_id, traftype_id, date_time, > bytes_in, bytes_out) > VALUES (new.login_id, new.traftype_id, new.date_time, new.bytes_in, > new.bytes_out) > n_traf_insert_y2007m07 AS > ON INSERT TO n_traf > WHERE new.date_time >= '2007-07-01'::date AND new.date_time < > '2007-08-01 00:00:00'::timestamp without time zone DO INSTEAD INSERT > INTO n_traf_y2007m07 (login_id, traftype_id, date_time, bytes_in, > bytes_out) > VALUES (new.login_id, new.traftype_id, new.date_time, new.bytes_in, > new.bytes_out) > n_traf_insert_y2007m08 AS > ON INSERT TO n_traf > WHERE new.date_time >= '2007-08-01'::date AND new.date_time < > '2007-09-01 00:00:00'::timestamp without time zone DO INSTEAD INSERT > INTO n_traf_y2007m08 (login_id, traftype_id, date_time, bytes_in, > bytes_out) > VALUES (new.login_id, new.traftype_id, new.date_time, new.bytes_in, > new.bytes_out) > n_traf_insert_y2007m09 AS > ON INSERT TO n_traf > WHERE new.date_time >= '2007-09-01'::date AND new.date_time < > '2007-10-01 00:00:00'::timestamp without time zone DO INSTEAD INSERT > INTO n_traf_y2007m09 (login_id, traftype_id, date_time, bytes_in, > bytes_out) > VALUES (new.login_id, new.traftype_id, new.date_time, new.bytes_in, > new.bytes_out) > n_traf_insert_y2007m10 AS > ON INSERT TO n_traf > WHERE new.date_time >= '2007-10-01'::date AND new.date_time < > '2007-11-01 00:00:00'::timestamp without time zone DO INSTEAD INSERT > INTO n_traf_y2007m10 (login_id, traftype_id, date_time, bytes_in, > bytes_out) > VALUES (new.login_id, new.traftype_id, new.date_time, new.bytes_in, > new.bytes_out) > n_traf_insert_y2007m11 AS > ON INSERT TO n_traf > WHERE new.date_time >= '2007-11-01'::date AND new.date_time < > '2007-12-01 00:00:00'::timestamp without time zone DO INSTEAD INSERT > INTO n_traf_y2007m11 (login_id, traftype_id, date_time, bytes_in, > bytes_out) > VALUES (new.login_id, new.traftype_id, new.date_time, new.bytes_in, > new.bytes_out) > n_traf_insert_y2007m12 AS > ON INSERT TO n_traf > WHERE new.date_time >= '2007-12-01'::date AND new.date_time < > '2008-01-01 00:00:00'::timestamp without time zone DO INSTEAD INSERT > INTO n_traf_y2007m12 (login_id, traftype_id, date_time, bytes_in, > bytes_out) > VALUES (new.login_id, new.traftype_id, new.date_time, new.bytes_in, > new.bytes_out) > > > Tables n_traf_y2007m... looks like these > > Table "public.n_traf_y2007m01" > Column | Type | Modifiers > -------------+-----------------------------+-------------------- > login_id | integer | not null > traftype_id | integer | not null > date_time | timestamp without time zone | not null > bytes_in | bigint | not null default 0 > bytes_out | bigint | not null default 0 > Indexes: > "n_traf_y2007m01_date_time_login_id" btree (date_time, login_id) > Check constraints: > "n_traf_y2007m01_date_time_check" CHECK (date_time >= > '2007-01-01'::date AND date_time < '2007-02-01 00:00:00'::timestamp > without time zone) > Inherits: n_traf > > Index "public.n_traf_y2007m01_date_time_login_id" > Column | Type > -----------+----------------------------- > date_time | timestamp without time zone > login_id | integer > btree, for table "public.n_traf_y2007m01" > > Table "public.n_traf_y2007m02" > Column | Type | Modifiers > -------------+-----------------------------+-------------------- > login_id | integer | not null > traftype_id | integer | not null > date_time | timestamp without time zone | not null > bytes_in | bigint | not null default 0 > bytes_out | bigint | not null default 0 > Indexes: > "n_traf_y2007m02_date_time_login_id" btree (date_time, login_id) > Check constraints: > "n_traf_y2007m02_date_time_check" CHECK (date_time >= > '2007-02-01'::date AND date_time < '2007-03-01 00:00:00'::timestamp > without time zone) > Inherits: n_traf > ... -- engineer