DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=SruFu+2rtV+FI+pA1FmNZLjDUUx7fHtgw33Ngrldt3UCYncDnv2icmFQ7GL5kR/bvtVPnhl/5rdx2qGIScLPkXUERGunxP7hNajAblhJsbZh78e8hU5p2lj/libzmDJv4q+po7JdbhR0jllMo9upBJA3EZLHoeVxubBU4487nYc=
Message-ID: <8cac8dd0708240153p4f3ef2d9xae912259926d860@mail.gmail.com>
Date: Fri, 24 Aug 2007 14:53:05 +0600
From: Anton <anton200@gmail.com>
To: pgsql-performance@postgresql.org
Subject: partitioned table and ORDER BY indexed_field DESC LIMIT 1
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi.

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