Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1

2007/10/27, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Anton <anton200(at)gmail(dot)com> 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