pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpartitioned table and ORDER BY indexed_field DESC LIMIT 1
33+ messages / 11 participants
[nested] [flat]
* partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-08-24 08:53 Anton <anton200@gmail.com>
0 siblings, 2 replies; 33+ messages in thread
From: Anton @ 2007-08-24 08:53 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-08-24 10:24 Mikko Partio <mpartio@gmail.com>
parent: Anton <anton200@gmail.com>
1 sibling, 1 reply; 33+ messages in thread
From: Mikko Partio @ 2007-08-24 10:24 UTC (permalink / raw)
To: Anton <anton200@gmail.com>; +Cc: pgsql-performance
On 8/24/07, Anton <anton200@gmail.com> wrote:
>
> 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?
> -------------------------------------------
I'm no expert but I'd guess that the the planner doesn't know which
partition holds the latest time so it has to read them all.
Regards
MP
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-08-24 10:32 Anton <anton200@gmail.com>
parent: Mikko Partio <mpartio@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: Anton @ 2007-08-24 10:32 UTC (permalink / raw)
To: pgsql-performance
> > =# 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_y2007m12 n_traf
> > (cost=0.00..22.30 rows=1230 width=32)
> > (18 rows)
> >
> > Why it no uses indexes at all?
> > -------------------------------------------
> I'm no expert but I'd guess that the the planner doesn't know which
> partition holds the latest time so it has to read them all.
Agree. But why it not uses indexes when it reading them?
--
engineer
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-08-24 10:38 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Anton <anton200@gmail.com>
0 siblings, 2 replies; 33+ messages in thread
From: Heikki Linnakangas @ 2007-08-24 10:38 UTC (permalink / raw)
To: Anton <anton200@gmail.com>; +Cc: pgsql-performance
Anton wrote:
>>> =# 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_y2007m12 n_traf
>>> (cost=0.00..22.30 rows=1230 width=32)
>>> (18 rows)
>>>
>>> Why it no uses indexes at all?
>>> -------------------------------------------
>> I'm no expert but I'd guess that the the planner doesn't know which
>> partition holds the latest time so it has to read them all.
>
> Agree. But why it not uses indexes when it reading them?
The planner isn't smart enough to push the "ORDER BY ... LIMIT ..."
below the append node. Therefore it needs to fetch all rows from all the
tables, and the fastest way to do that is a seq scan.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-08-24 16:20 Luke Lonergan <llonergan@greenplum.com>
parent: Heikki Linnakangas <heikki@enterprisedb.com>
1 sibling, 0 replies; 33+ messages in thread
From: Luke Lonergan @ 2007-08-24 16:20 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; +Cc: pgsql-performance
We just fixed this - I'll post a patch, but I don't have time to verify
against HEAD.
- Luke
On 8/24/07 3:38 AM, "Heikki Linnakangas" <heikki@enterprisedb.com> wrote:
> Anton wrote:
>>>> =# 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_y2007m12 n_traf
>>>> (cost=0.00..22.30 rows=1230 width=32)
>>>> (18 rows)
>>>>
>>>> Why it no uses indexes at all?
>>>> -------------------------------------------
>>> I'm no expert but I'd guess that the the planner doesn't know which
>>> partition holds the latest time so it has to read them all.
>>
>> Agree. But why it not uses indexes when it reading them?
>
> The planner isn't smart enough to push the "ORDER BY ... LIMIT ..."
> below the append node. Therefore it needs to fetch all rows from all the
> tables, and the fastest way to do that is a seq scan.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-08-24 16:25 Luke Lonergan <llonergan@greenplum.com>
parent: Heikki Linnakangas <heikki@enterprisedb.com>
1 sibling, 2 replies; 33+ messages in thread
From: Luke Lonergan @ 2007-08-24 16:25 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; +Cc: pgsql-performance
Below is a patch against 8.2.4 (more or less), Heikki can you take a look at
it?
This enables the use of index scan of a child table by recognizing sort
order of the append node. Kurt Harriman did the work.
- Luke
Index: cdb-pg/src/backend/optimizer/path/indxpath.c
===================================================================
RCS file:
/data/FISHEYE_REPOSITORIES/greenplum/cvsroot/cdb2/cdb-pg/src/backend/optimiz
er/path/indxpath.c,v
diff -u -N -r1.22 -r1.22.2.1
--- cdb-pg/src/backend/optimizer/path/indxpath.c 25 Apr 2007 22:07:21
-0000 1.22
+++ cdb-pg/src/backend/optimizer/path/indxpath.c 10 Aug 2007 03:41:15
-0000 1.22.2.1
@@ -379,8 +379,51 @@
index_pathkeys = build_index_pathkeys(root, index,
ForwardScanDirection,
true);
- useful_pathkeys = truncate_useless_pathkeys(root, rel,
- index_pathkeys);
+ /*
+ * CDB: For appendrel child, pathkeys contain Var nodes in
terms
+ * of the child's baserel. Transform the pathkey list to refer
to
+ * columns of the appendrel.
+ */
+ if (rel->reloptkind == RELOPT_OTHER_MEMBER_REL)
+ {
+ AppendRelInfo *appinfo = NULL;
+ RelOptInfo *appendrel = NULL;
+ ListCell *appcell;
+ CdbPathLocus notalocus;
+
+ /* Find the appendrel of which this baserel is a child. */
+ foreach(appcell, root->append_rel_list)
+ {
+ appinfo = (AppendRelInfo *)lfirst(appcell);
+ if (appinfo->child_relid == rel->relid)
+ break;
+ }
+ Assert(appinfo);
+ appendrel = find_base_rel(root, appinfo->parent_relid);
+
+ /*
+ * The pathkey list happens to have the same format as the
+ * partitioning key of a Hashed locus, so by disguising it
+ * we can use cdbpathlocus_pull_above_projection() to do
the
+ * transformation.
+ */
+ CdbPathLocus_MakeHashed(¬alocus, index_pathkeys);
+ notalocus =
+ cdbpathlocus_pull_above_projection(root,
+ notalocus,
+ rel->relids,
+ rel->reltargetlist,
+
appendrel->reltargetlist,
+ appendrel->relid);
+ if (CdbPathLocus_IsHashed(notalocus))
+ index_pathkeys = truncate_useless_pathkeys(root,
appendrel,
+
notalocus.partkey);
+ else
+ index_pathkeys = NULL;
+ }
+
+ useful_pathkeys = truncate_useless_pathkeys(root, rel,
+ index_pathkeys);
}
else
useful_pathkeys = NIL;
Index: cdb-pg/src/backend/optimizer/path/pathkeys.c
===================================================================
RCS file:
/data/FISHEYE_REPOSITORIES/greenplum/cvsroot/cdb2/cdb-pg/src/backend/optimiz
er/path/pathkeys.c,v
diff -u -N -r1.18 -r1.18.2.1
--- cdb-pg/src/backend/optimizer/path/pathkeys.c 30 Apr 2007 05:44:07
-0000 1.18
+++ cdb-pg/src/backend/optimizer/path/pathkeys.c 10 Aug 2007 03:41:15
-0000 1.18.2.1
@@ -1403,55 +1403,53 @@
{
PathKeyItem *item;
Expr *newexpr;
+ AttrNumber targetindex;
Assert(pathkey);
- /* Use constant expr if available. Will be at head of list. */
- if (CdbPathkeyEqualsConstant(pathkey))
+ /* Find an expr that we can rewrite to use the projected columns. */
+ item = cdbpullup_findPathKeyItemInTargetList(pathkey,
+ relids,
+ targetlist,
+ &targetindex); // OUT
+
+ /* If not found, see if the equiv class contains a constant expr. */
+ if (!item &&
+ CdbPathkeyEqualsConstant(pathkey))
{
item = (PathKeyItem *)linitial(pathkey);
newexpr = (Expr *)copyObject(item->key);
}
- /* New vars for old! */
- else
- {
- AttrNumber targetindex;
+ /* Fail if no usable expr. */
+ else if (!item)
+ return NULL;
- /* Find an expr that we can rewrite to use the projected columns.
*/
- item = cdbpullup_findPathKeyItemInTargetList(pathkey,
- relids,
- targetlist,
- &targetindex); // OUT
- if (!item)
- return NULL;
+ /* If found matching targetlist item, make a Var that references it. */
+ else if (targetindex > 0)
+ newexpr = (Expr *)cdbpullup_makeVar(newrelid,
+ targetindex,
+ newvarlist,
+ (Expr *)item->key);
- /* If found matching targetlist item, make a Var that references
it. */
- if (targetindex > 0)
- newexpr = (Expr *)cdbpullup_makeVar(newrelid,
- targetindex,
- newvarlist,
- (Expr *)item->key);
+ /* Replace expr's Var nodes with new ones referencing the targetlist.
*/
+ else
+ newexpr = cdbpullup_expr((Expr *)item->key,
+ targetlist,
+ newvarlist,
+ newrelid);
- /* Replace expr's Var nodes with new ones referencing the
targetlist. */
- else
- newexpr = cdbpullup_expr((Expr *)item->key,
- targetlist,
- newvarlist,
- newrelid);
+ /* Pull up RelabelType node too, unless tlist expr has right type. */
+ if (IsA(item->key, RelabelType))
+ {
+ RelabelType *oldrelabel = (RelabelType *)item->key;
- /* Pull up RelabelType node too, unless tlist expr has right type.
*/
- if (IsA(item->key, RelabelType))
- {
- RelabelType *oldrelabel = (RelabelType *)item->key;
-
- if (oldrelabel->resulttype != exprType((Node *)newexpr) ||
- oldrelabel->resulttypmod != exprTypmod((Node *)newexpr))
- newexpr = (Expr *)makeRelabelType(newexpr,
- oldrelabel->resulttype,
- oldrelabel->resulttypmod,
-
oldrelabel->relabelformat);
- }
+ if (oldrelabel->resulttype != exprType((Node *)newexpr) ||
+ oldrelabel->resulttypmod != exprTypmod((Node *)newexpr))
+ newexpr = (Expr *)makeRelabelType(newexpr,
+ oldrelabel->resulttype,
+ oldrelabel->resulttypmod,
+ oldrelabel->relabelformat);
}
Insist(newexpr);
Index: cdb-pg/src/backend/optimizer/util/pathnode.c
===================================================================
RCS file:
/data/FISHEYE_REPOSITORIES/greenplum/cvsroot/cdb2/cdb-pg/src/backend/optimiz
er/util/pathnode.c,v
diff -u -N -r1.52.2.4 -r1.52.2.5
--- cdb-pg/src/backend/optimizer/util/pathnode.c 5 Aug 2007 23:06:44
-0000 1.52.2.4
+++ cdb-pg/src/backend/optimizer/util/pathnode.c 10 Aug 2007 03:41:15
-0000 1.52.2.5
@@ -1563,7 +1563,15 @@
pathnode->path.rescannable = false;
}
- return pathnode;
+ /*
+ * CDB: If there is exactly one subpath, its ordering is preserved.
+ * Child rel's pathkey exprs are already expressed in terms of the
+ * columns of the parent appendrel. See find_usable_indexes().
+ */
+ if (list_length(subpaths) == 1)
+ pathnode->path.pathkeys = ((Path *)linitial(subpaths))->pathkeys;
+
+ return pathnode;
}
/*
On 8/24/07 3:38 AM, "Heikki Linnakangas" <heikki@enterprisedb.com> wrote:
> Anton wrote:
>>>> =# 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_y2007m12 n_traf
>>>> (cost=0.00..22.30 rows=1230 width=32)
>>>> (18 rows)
>>>>
>>>> Why it no uses indexes at all?
>>>> -------------------------------------------
>>> I'm no expert but I'd guess that the the planner doesn't know which
>>> partition holds the latest time so it has to read them all.
>>
>> Agree. But why it not uses indexes when it reading them?
>
> The planner isn't smart enough to push the "ORDER BY ... LIMIT ..."
> below the append node. Therefore it needs to fetch all rows from all the
> tables, and the fastest way to do that is a seq scan.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-08-27 12:57 Tomas Tamosaitis <tomas.tamosaitis@connecty.lt>
parent: Anton <anton200@gmail.com>
1 sibling, 0 replies; 33+ messages in thread
From: Tomas Tamosaitis @ 2007-08-27 12:57 UTC (permalink / raw)
To: pgsql-performance
Pn, 2007 08 24 14:53 +0600, Anton rašė:
> 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 PL
can you test performance and send explain results of select like this :
select * from n_traf where date_time = (select max(date_time) from
n_traf);
i have similar problem with ~70M rows table (then using ordering), but
my table not partitioned.
I`m interesting how this select will works on partitioned table.
--
Pagarbiai,
Tomas Tamošaitis
Projektų Vadovas
Connecty
Skype://mazgis1009?add
Mob: +370 652 86127
e-pastas: tomas.tamosaitis@connecty.lt
web: www.connecty.lt
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-08-28 10:20 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Luke Lonergan <llonergan@greenplum.com>
1 sibling, 1 reply; 33+ messages in thread
From: Heikki Linnakangas @ 2007-08-28 10:20 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; Bruce Momjian <bruce@momjian.us>; +Cc: Anton <anton200@gmail.com>; pgsql-performance
Bruce, would you please add this to the 8.4 patch queue so we remember
to look at this later?
It didn't occur to me that we can do that in the degenerate case when
there's just a single node below the Append. A more general solution
would be to check if the pathkeys of all the child nodes match, and do a
"merge append" similar to a merge join.
Luke Lonergan wrote:
> Below is a patch against 8.2.4 (more or less), Heikki can you take a look at
> it?
>
> This enables the use of index scan of a child table by recognizing sort
> order of the append node. Kurt Harriman did the work.
>
> - Luke
>
> Index: cdb-pg/src/backend/optimizer/path/indxpath.c
> ===================================================================
> RCS file:
> /data/FISHEYE_REPOSITORIES/greenplum/cvsroot/cdb2/cdb-pg/src/backend/optimiz
> er/path/indxpath.c,v
> diff -u -N -r1.22 -r1.22.2.1
> --- cdb-pg/src/backend/optimizer/path/indxpath.c 25 Apr 2007 22:07:21
> -0000 1.22
> +++ cdb-pg/src/backend/optimizer/path/indxpath.c 10 Aug 2007 03:41:15
> -0000 1.22.2.1
> @@ -379,8 +379,51 @@
> index_pathkeys = build_index_pathkeys(root, index,
> ForwardScanDirection,
> true);
> - useful_pathkeys = truncate_useless_pathkeys(root, rel,
> - index_pathkeys);
> + /*
> + * CDB: For appendrel child, pathkeys contain Var nodes in
> terms
> + * of the child's baserel. Transform the pathkey list to refer
> to
> + * columns of the appendrel.
> + */
> + if (rel->reloptkind == RELOPT_OTHER_MEMBER_REL)
> + {
> + AppendRelInfo *appinfo = NULL;
> + RelOptInfo *appendrel = NULL;
> + ListCell *appcell;
> + CdbPathLocus notalocus;
> +
> + /* Find the appendrel of which this baserel is a child. */
> + foreach(appcell, root->append_rel_list)
> + {
> + appinfo = (AppendRelInfo *)lfirst(appcell);
> + if (appinfo->child_relid == rel->relid)
> + break;
> + }
> + Assert(appinfo);
> + appendrel = find_base_rel(root, appinfo->parent_relid);
> +
> + /*
> + * The pathkey list happens to have the same format as the
> + * partitioning key of a Hashed locus, so by disguising it
> + * we can use cdbpathlocus_pull_above_projection() to do
> the
> + * transformation.
> + */
> + CdbPathLocus_MakeHashed(¬alocus, index_pathkeys);
> + notalocus =
> + cdbpathlocus_pull_above_projection(root,
> + notalocus,
> + rel->relids,
> + rel->reltargetlist,
> +
> appendrel->reltargetlist,
> + appendrel->relid);
> + if (CdbPathLocus_IsHashed(notalocus))
> + index_pathkeys = truncate_useless_pathkeys(root,
> appendrel,
> +
> notalocus.partkey);
> + else
> + index_pathkeys = NULL;
> + }
> +
> + useful_pathkeys = truncate_useless_pathkeys(root, rel,
> + index_pathkeys);
> }
> else
> useful_pathkeys = NIL;
> Index: cdb-pg/src/backend/optimizer/path/pathkeys.c
> ===================================================================
> RCS file:
> /data/FISHEYE_REPOSITORIES/greenplum/cvsroot/cdb2/cdb-pg/src/backend/optimiz
> er/path/pathkeys.c,v
> diff -u -N -r1.18 -r1.18.2.1
> --- cdb-pg/src/backend/optimizer/path/pathkeys.c 30 Apr 2007 05:44:07
> -0000 1.18
> +++ cdb-pg/src/backend/optimizer/path/pathkeys.c 10 Aug 2007 03:41:15
> -0000 1.18.2.1
> @@ -1403,55 +1403,53 @@
> {
> PathKeyItem *item;
> Expr *newexpr;
> + AttrNumber targetindex;
>
> Assert(pathkey);
>
> - /* Use constant expr if available. Will be at head of list. */
> - if (CdbPathkeyEqualsConstant(pathkey))
> + /* Find an expr that we can rewrite to use the projected columns. */
> + item = cdbpullup_findPathKeyItemInTargetList(pathkey,
> + relids,
> + targetlist,
> + &targetindex); // OUT
> +
> + /* If not found, see if the equiv class contains a constant expr. */
> + if (!item &&
> + CdbPathkeyEqualsConstant(pathkey))
> {
> item = (PathKeyItem *)linitial(pathkey);
> newexpr = (Expr *)copyObject(item->key);
> }
>
> - /* New vars for old! */
> - else
> - {
> - AttrNumber targetindex;
> + /* Fail if no usable expr. */
> + else if (!item)
> + return NULL;
>
> - /* Find an expr that we can rewrite to use the projected columns.
> */
> - item = cdbpullup_findPathKeyItemInTargetList(pathkey,
> - relids,
> - targetlist,
> - &targetindex); // OUT
> - if (!item)
> - return NULL;
> + /* If found matching targetlist item, make a Var that references it. */
> + else if (targetindex > 0)
> + newexpr = (Expr *)cdbpullup_makeVar(newrelid,
> + targetindex,
> + newvarlist,
> + (Expr *)item->key);
>
> - /* If found matching targetlist item, make a Var that references
> it. */
> - if (targetindex > 0)
> - newexpr = (Expr *)cdbpullup_makeVar(newrelid,
> - targetindex,
> - newvarlist,
> - (Expr *)item->key);
> + /* Replace expr's Var nodes with new ones referencing the targetlist.
> */
> + else
> + newexpr = cdbpullup_expr((Expr *)item->key,
> + targetlist,
> + newvarlist,
> + newrelid);
>
> - /* Replace expr's Var nodes with new ones referencing the
> targetlist. */
> - else
> - newexpr = cdbpullup_expr((Expr *)item->key,
> - targetlist,
> - newvarlist,
> - newrelid);
> + /* Pull up RelabelType node too, unless tlist expr has right type. */
> + if (IsA(item->key, RelabelType))
> + {
> + RelabelType *oldrelabel = (RelabelType *)item->key;
>
> - /* Pull up RelabelType node too, unless tlist expr has right type.
> */
> - if (IsA(item->key, RelabelType))
> - {
> - RelabelType *oldrelabel = (RelabelType *)item->key;
> -
> - if (oldrelabel->resulttype != exprType((Node *)newexpr) ||
> - oldrelabel->resulttypmod != exprTypmod((Node *)newexpr))
> - newexpr = (Expr *)makeRelabelType(newexpr,
> - oldrelabel->resulttype,
> - oldrelabel->resulttypmod,
> -
> oldrelabel->relabelformat);
> - }
> + if (oldrelabel->resulttype != exprType((Node *)newexpr) ||
> + oldrelabel->resulttypmod != exprTypmod((Node *)newexpr))
> + newexpr = (Expr *)makeRelabelType(newexpr,
> + oldrelabel->resulttype,
> + oldrelabel->resulttypmod,
> + oldrelabel->relabelformat);
> }
> Insist(newexpr);
>
> Index: cdb-pg/src/backend/optimizer/util/pathnode.c
> ===================================================================
> RCS file:
> /data/FISHEYE_REPOSITORIES/greenplum/cvsroot/cdb2/cdb-pg/src/backend/optimiz
> er/util/pathnode.c,v
> diff -u -N -r1.52.2.4 -r1.52.2.5
> --- cdb-pg/src/backend/optimizer/util/pathnode.c 5 Aug 2007 23:06:44
> -0000 1.52.2.4
> +++ cdb-pg/src/backend/optimizer/util/pathnode.c 10 Aug 2007 03:41:15
> -0000 1.52.2.5
> @@ -1563,7 +1563,15 @@
> pathnode->path.rescannable = false;
> }
>
> - return pathnode;
> + /*
> + * CDB: If there is exactly one subpath, its ordering is preserved.
> + * Child rel's pathkey exprs are already expressed in terms of the
> + * columns of the parent appendrel. See find_usable_indexes().
> + */
> + if (list_length(subpaths) == 1)
> + pathnode->path.pathkeys = ((Path *)linitial(subpaths))->pathkeys;
> +
> + return pathnode;
> }
>
> /*
>
>
> On 8/24/07 3:38 AM, "Heikki Linnakangas" <heikki@enterprisedb.com> wrote:
>
>> Anton wrote:
>>>>> =# 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_y2007m12 n_traf
>>>>> (cost=0.00..22.30 rows=1230 width=32)
>>>>> (18 rows)
>>>>>
>>>>> Why it no uses indexes at all?
>>>>> -------------------------------------------
>>>> I'm no expert but I'd guess that the the planner doesn't know which
>>>> partition holds the latest time so it has to read them all.
>>> Agree. But why it not uses indexes when it reading them?
>> The planner isn't smart enough to push the "ORDER BY ... LIMIT ..."
>> below the append node. Therefore it needs to fetch all rows from all the
>> tables, and the fastest way to do that is a seq scan.
>
>
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-10-27 04:26 Anton <anton200@gmail.com>
parent: Luke Lonergan <llonergan@greenplum.com>
1 sibling, 1 reply; 33+ messages in thread
From: Anton @ 2007-10-27 04:26 UTC (permalink / raw)
To: pgsql-performance
I want ask about problem with partioned tables (it was discussed some
time ago, see below). Is it fixed somehow in 8.2.5 ?
2007/8/24, Luke Lonergan <llonergan@greenplum.com>:
> Below is a patch against 8.2.4 (more or less), Heikki can you take a look at
> it?
>
> This enables the use of index scan of a child table by recognizing sort
> order of the append node. Kurt Harriman did the work.
...
>
> On 8/24/07 3:38 AM, "Heikki Linnakangas" <heikki@enterprisedb.com> wrote:
>
> > Anton wrote:
> >>>> =# 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_y2007m12 n_traf
> >>>> (cost=0.00..22.30 rows=1230 width=32)
> >>>> (18 rows)
> >>>>
> >>>> Why it no uses indexes at all?
> >>>> -------------------------------------------
> >>> I'm no expert but I'd guess that the the planner doesn't know which
> >>> partition holds the latest time so it has to read them all.
> >>
> >> Agree. But why it not uses indexes when it reading them?
> >
> > The planner isn't smart enough to push the "ORDER BY ... LIMIT ..."
> > below the append node. Therefore it needs to fetch all rows from all the
> > tables, and the fastest way to do that is a seq scan.
--
engineer
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-10-27 05:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: Anton <anton200@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: Tom Lane @ 2007-10-27 05:37 UTC (permalink / raw)
To: Anton <anton200@gmail.com>; +Cc: pgsql-performance
Anton <anton200@gmail.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
not even compile in community Postgres, because it adds calls to half a
dozen Greenplum routines that we've never seen. Not to mention that
the base of the diff is Greenplum proprietary code, so the patch itself
wouldn't even apply successfully.
As to whether it would work if we had the full story ... well, not
having the full story, I don't want to opine.
regards, tom lane
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-10-27 08:53 Anton <anton200@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 33+ messages in thread
From: Anton @ 2007-10-27 08:53 UTC (permalink / raw)
To: pgsql-performance
2007/10/27, Tom Lane <tgl@sss.pgh.pa.us>:
> Anton <anton200@gmail.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
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-10-27 09:11 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Anton <anton200@gmail.com>
0 siblings, 0 replies; 33+ messages in thread
From: Heikki Linnakangas @ 2007-10-27 09:11 UTC (permalink / raw)
To: Anton <anton200@gmail.com>; +Cc: pgsql-performance
Anton wrote:
> 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.
As I said back then:
The planner isn't smart enough to push the "ORDER BY ... LIMIT ..."
below the append node.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-10-27 19:12 Luke Lonergan <LLonergan@greenplum.com>
0 siblings, 2 replies; 33+ messages in thread
From: Luke Lonergan @ 2007-10-27 19:12 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; +Cc: pgsql-performance
And I repeat - 'we fixed that and submitted a patch' - you can find it in the unapplied patches queue.
The patch isn't ready for application, but someone can quickly implement it I'd expect.
- Luke
Msg is shrt cuz m on ma treo
-----Original Message-----
From: Heikki Linnakangas [mailto:heikki@enterprisedb.com]
Sent: Saturday, October 27, 2007 05:20 AM Eastern Standard Time
To: Anton
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] partitioned table and ORDER BY indexed_field DESC LIMIT 1
Anton wrote:
> 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.
As I said back then:
The planner isn't smart enough to push the "ORDER BY ... LIMIT ..."
below the append node.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-10-27 19:28 Luke Lonergan <LLonergan@greenplum.com>
0 siblings, 1 reply; 33+ messages in thread
From: Luke Lonergan @ 2007-10-27 19:28 UTC (permalink / raw)
To: Luke Lonergan <LLonergan@greenplum.com>; Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; +Cc: pgsql-performance
I just read the lead ups to this post - didn't see Tom and Greg's comments.
The approach we took was to recognize the ordering of child nodes and propagate that to the append in the special case of only one child (after CE). This is the most common use-case in 'partitioning', and so is an easy, high payoff low amount of code fix.
I'd suggest we take this approach while also considering a more powerful set of append merge capabilities.
- Luke
Msg is shrt cuz m on ma treo
-----Original Message-----
From: Luke Lonergan [mailto:LLonergan@greenplum.com]
Sent: Saturday, October 27, 2007 03:14 PM Eastern Standard Time
To: Heikki Linnakangas; Anton
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] partitioned table and ORDER BY indexed_field DESC LIMIT 1
And I repeat - 'we fixed that and submitted a patch' - you can find it in the unapplied patches queue.
The patch isn't ready for application, but someone can quickly implement it I'd expect.
- Luke
Msg is shrt cuz m on ma treo
-----Original Message-----
From: Heikki Linnakangas [mailto:heikki@enterprisedb.com]
Sent: Saturday, October 27, 2007 05:20 AM Eastern Standard Time
To: Anton
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] partitioned table and ORDER BY indexed_field DESC LIMIT 1
Anton wrote:
> 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.
As I said back then:
The planner isn't smart enough to push the "ORDER BY ... LIMIT ..."
below the append node.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-10-27 21:31 Simon Riggs <simon@2ndquadrant.com>
parent: Luke Lonergan <LLonergan@greenplum.com>
1 sibling, 0 replies; 33+ messages in thread
From: Simon Riggs @ 2007-10-27 21:31 UTC (permalink / raw)
To: Luke Lonergan <LLonergan@greenplum.com>; +Cc: Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; pgsql-performance
On Sat, 2007-10-27 at 15:12 -0400, Luke Lonergan wrote:
> And I repeat - 'we fixed that and submitted a patch' - you can find it
> in the unapplied patches queue.
I got the impression it was a suggestion rather than a tested patch,
forgive me if that was wrong.
Did the patch work? Do you have timings/different plan?
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESCLIMIT 1
@ 2007-10-27 21:48 Luke Lonergan <LLonergan@greenplum.com>
0 siblings, 1 reply; 33+ messages in thread
From: Luke Lonergan @ 2007-10-27 21:48 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; pgsql-performance
Works great - plans no longer sort, but rather use indices as expected. It's in use in Greenplum now.
It's a simple approach, should easily extend from gpdb to postgres. The patch is against gpdb so someone needs to 'port' it.
- Luke
Msg is shrt cuz m on ma treo
-----Original Message-----
From: Simon Riggs [mailto:simon@2ndquadrant.com]
Sent: Saturday, October 27, 2007 05:34 PM Eastern Standard Time
To: Luke Lonergan
Cc: Heikki Linnakangas; Anton; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] partitioned table and ORDER BY indexed_field DESCLIMIT 1
On Sat, 2007-10-27 at 15:12 -0400, Luke Lonergan wrote:
> And I repeat - 'we fixed that and submitted a patch' - you can find it
> in the unapplied patches queue.
I got the impression it was a suggestion rather than a tested patch,
forgive me if that was wrong.
Did the patch work? Do you have timings/different plan?
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-10-28 00:20 Gregory Stark <stark@enterprisedb.com>
parent: Luke Lonergan <LLonergan@greenplum.com>
0 siblings, 0 replies; 33+ messages in thread
From: Gregory Stark @ 2007-10-28 00:20 UTC (permalink / raw)
To: Luke Lonergan <LLonergan@greenplum.com>; +Cc: Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; pgsql-performance
"Luke Lonergan" <LLonergan@greenplum.com> writes:
> The approach we took was to recognize the ordering of child nodes and
> propagate that to the append in the special case of only one child (after
> CE). This is the most common use-case in 'partitioning', and so is an easy,
> high payoff low amount of code fix.
Ah yes, we should definitely try to prune singleton append nodes. On a lark I
had tried to do precisely that to see what would happen but ran into precisely
the problem you had to solve here with your pullup_vars function. That's one
of the functions which wasn't included in the original patch so I'll look at
the patch from the queue to see what's involved.
Actually currently it's not a common case because we can't eliminate the
parent partition. I have some ideas for how to deal with that but haven't
written them up yet.
In theory if we can preserve ordering across append nodes there's no good
reason to prune them. But generally I think simplifying the plan is good if
only to present simpler plans to the user.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESCLIMIT 1
@ 2007-10-28 09:13 Simon Riggs <simon@2ndquadrant.com>
parent: Luke Lonergan <LLonergan@greenplum.com>
0 siblings, 1 reply; 33+ messages in thread
From: Simon Riggs @ 2007-10-28 09:13 UTC (permalink / raw)
To: Luke Lonergan <LLonergan@greenplum.com>; +Cc: Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; pgsql-performance
On Sat, 2007-10-27 at 17:48 -0400, Luke Lonergan wrote:
> Works great - plans no longer sort, but rather use indices as
> expected. It's in use in Greenplum now.
>
> It's a simple approach, should easily extend from gpdb to postgres.
> The patch is against gpdb so someone needs to 'port' it.
The part of the patch that didn't work for me was the nrels==1 bit. The
way it currently works there is only ever 0 or 2+ rels. The normal
Postgres code has to cater for the possibility of a non-empty parent
table, which seems to destroy the possibility of using this technique.
I agree its annoying and I have a way of doing this, but that's an 8.4
thing now.
Anybody think different?
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESCLIMIT 1
@ 2007-10-28 16:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 33+ messages in thread
From: Tom Lane @ 2007-10-28 16:53 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Luke Lonergan <LLonergan@greenplum.com>; Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; pgsql-performance
Simon Riggs <simon@2ndquadrant.com> writes:
> I agree its annoying and I have a way of doing this, but that's an 8.4
> thing now.
It was an 8.4 thing quite some time ago, since no working patch was ever
submitted.
regards, tom lane
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESCLIMIT 1
@ 2007-10-28 17:27 Simon Riggs <simon@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 33+ messages in thread
From: Simon Riggs @ 2007-10-28 17:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Sun, 2007-10-28 at 12:53 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > I agree its annoying and I have a way of doing this, but that's an 8.4
> > thing now.
>
> It was an 8.4 thing quite some time ago, since no working patch was ever
> submitted.
Sorry, I meant that the problem itself is annoying, not anything to do
with patches. I'm very happy with all that we've done.
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-10-29 13:40 Gregory Stark <stark@enterprisedb.com>
parent: Luke Lonergan <LLonergan@greenplum.com>
1 sibling, 1 reply; 33+ messages in thread
From: Gregory Stark @ 2007-10-29 13:40 UTC (permalink / raw)
To: Luke Lonergan <LLonergan@greenplum.com>; +Cc: Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; pgsql-performance
"Luke Lonergan" <LLonergan@greenplum.com> writes:
> And I repeat - 'we fixed that and submitted a patch' - you can find it in the unapplied patches queue.
I can't find this. Can you point me towards it?
Thanks
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask about EnterpriseDB's PostGIS support!
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-10-30 05:06 Luke Lonergan <llonergan@greenplum.com>
parent: Gregory Stark <stark@enterprisedb.com>
0 siblings, 2 replies; 33+ messages in thread
From: Luke Lonergan @ 2007-10-30 05:06 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; pgsql-performance
Sure - it's here:
http://momjian.us/mhonarc/patches_hold/msg00381.html
- Luke
On 10/29/07 6:40 AM, "Gregory Stark" <stark@enterprisedb.com> wrote:
> "Luke Lonergan" <LLonergan@greenplum.com> writes:
>
>> And I repeat - 'we fixed that and submitted a patch' - you can find it in the
>> unapplied patches queue.
>
> I can't find this. Can you point me towards it?
>
> Thanks
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-10-30 05:46 Mark Kirkwood <markir@paradise.net.nz>
parent: Luke Lonergan <llonergan@greenplum.com>
1 sibling, 2 replies; 33+ messages in thread
From: Mark Kirkwood @ 2007-10-30 05:46 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: Gregory Stark <stark@enterprisedb.com>; Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; pgsql-performance
Luke Lonergan wrote:
> Sure - it's here:
> http://momjian.us/mhonarc/patches_hold/msg00381.html
>
>
To clarify - we've fixed this in Greenplum db - the patch as submitted
is (hopefully) a hint about how to fix it in Postgres, rather than a
working patch... as its full of non-postgres functions and macros:
CdbPathLocus_MakeHashed
cdbpathlocus_pull_above_projection
cdbpullup_findPathKeyItemInTargetList
cdbpullup_makeVar
cdbpullup_expr
Cheers
Mark
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-10-30 05:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Luke Lonergan <llonergan@greenplum.com>
1 sibling, 0 replies; 33+ messages in thread
From: Tom Lane @ 2007-10-30 05:54 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: Gregory Stark <stark@enterprisedb.com>; Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; pgsql-performance
"Luke Lonergan" <llonergan@greenplum.com> writes:
> Sure - it's here:
> http://momjian.us/mhonarc/patches_hold/msg00381.html
Luke, this is not a patch, and I'm getting pretty dang tired of seeing
you refer to it as one. What this is is a very-selective extract from
Greenplum proprietary code. If you'd like us to think it is a patch,
you need to offer the source code to all the GP-specific functions that
are called in the quoted additions.
Hell, the diff is *against* GP-specific code --- it removes calls
to functions that we've never seen, eg here:
- /* Use constant expr if available. Will be at head of list. */
- if (CdbPathkeyEqualsConstant(pathkey))
This is not a patch, and your statements that it's only a minor porting
matter to turn it into one are lie^H^H^Hnonsense. Please lift the
skirts higher than the ankle region if you want us to get excited.
regards, tom lane
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-10-30 09:06 Gregory Stark <stark@enterprisedb.com>
parent: Mark Kirkwood <markir@paradise.net.nz>
1 sibling, 1 reply; 33+ messages in thread
From: Gregory Stark @ 2007-10-30 09:06 UTC (permalink / raw)
To: Mark Kirkwood <markir@paradise.net.nz>; +Cc: Luke Lonergan <llonergan@greenplum.com>; Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; pgsql-performance
"Mark Kirkwood" <markir@paradise.net.nz> writes:
> Luke Lonergan wrote:
>> Sure - it's here:
>> http://momjian.us/mhonarc/patches_hold/msg00381.html
>
> To clarify - we've fixed this in Greenplum db - the patch as submitted is
> (hopefully) a hint about how to fix it in Postgres, rather than a working
> patch... as its full of non-postgres functions and macros:
Oh, that was the problem with the original patch and I thought Luke had said
that was the problem which was fixed.
> cdbpathlocus_pull_above_projection
In particular this is the function I was hoping to see. Anyways as Tom pointed
out previously there's precedent in Postgres as well for subqueries so I'm
sure I'll be able to do it.
(But I'm still not entirely convinced putting the append member vars into the
eclasses would be wrong btw...)
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-10-31 14:16 Luke Lonergan <llonergan@greenplum.com>
parent: Mark Kirkwood <markir@paradise.net.nz>
1 sibling, 0 replies; 33+ messages in thread
From: Luke Lonergan @ 2007-10-31 14:16 UTC (permalink / raw)
To: Mark Kirkwood <markir@paradise.net.nz>; +Cc: Gregory Stark <stark@enterprisedb.com>; Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; pgsql-performance
BTW - Mark has volunteered to work a Postgres patch together. Thanks Mark!
- Luke
On 10/29/07 10:46 PM, "Mark Kirkwood" <markir@paradise.net.nz> wrote:
> Luke Lonergan wrote:
>> Sure - it's here:
>> http://momjian.us/mhonarc/patches_hold/msg00381.html
>>
>>
>
> To clarify - we've fixed this in Greenplum db - the patch as submitted
> is (hopefully) a hint about how to fix it in Postgres, rather than a
> working patch... as its full of non-postgres functions and macros:
>
> CdbPathLocus_MakeHashed
> cdbpathlocus_pull_above_projection
> cdbpullup_findPathKeyItemInTargetList
> cdbpullup_makeVar
> cdbpullup_expr
>
> Cheers
>
> Mark
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-11-05 05:49 Mark Kirkwood <markir@paradise.net.nz>
parent: Gregory Stark <stark@enterprisedb.com>
0 siblings, 1 reply; 33+ messages in thread
From: Mark Kirkwood @ 2007-11-05 05:49 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: Luke Lonergan <llonergan@greenplum.com>; Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; pgsql-performance
Gregory Stark wrote:
> cdbpathlocus_pull_above_projection
>
>
> In particular this is the function I was hoping to see. Anyways as Tom pointed
> out previously there's precedent in Postgres as well for subqueries so I'm
> sure I'll be able to do it.
>
> (But I'm still not entirely convinced putting the append member vars into the
> eclasses would be wrong btw...)
>
>
I spent today looking at getting this patch into a self contained state.
Working against HEAD I'm getting bogged down in the PathKeyItem to
PathKey/EquivalenceClass/EquivalenceMember(s) change. So I figured I'd
divide and conquer to some extent, and initially provide a patch:
- against 8.2.(5)
- self contained (i.e no mystery functions)
The next step would be to update to to HEAD. That would hopefully
provide some useful material for others working on this.
Thoughts suggestions?
regards
Mark
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-11-05 12:12 Gregory Stark <stark@enterprisedb.com>
parent: Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 1 reply; 33+ messages in thread
From: Gregory Stark @ 2007-11-05 12:12 UTC (permalink / raw)
To: Mark Kirkwood <markir@paradise.net.nz>; +Cc: Luke Lonergan <llonergan@greenplum.com>; Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; pgsql-performance
"Mark Kirkwood" <markir@paradise.net.nz> writes:
> I spent today looking at getting this patch into a self contained state.
> Working against HEAD I'm getting bogged down in the PathKeyItem to
> PathKey/EquivalenceClass/EquivalenceMember(s) change. So I figured I'd divide
> and conquer to some extent, and initially provide a patch:
>
> - against 8.2.(5)
> - self contained (i.e no mystery functions)
That would be helpful for me. It would include the bits I'm looking for.
> The next step would be to update to to HEAD. That would hopefully provide some
> useful material for others working on this.
If that's not too much work then that would be great but if it's a lot of work
then it may not be worth it if I'm planning to only take certain bits. On the
other hand if it's good then we might just want to take it wholesale and then
add to it.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-11-08 02:46 Mark Kirkwood <markir@paradise.net.nz>
parent: Gregory Stark <stark@enterprisedb.com>
0 siblings, 1 reply; 33+ messages in thread
From: Mark Kirkwood @ 2007-11-08 02:46 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: Luke Lonergan <llonergan@greenplum.com>; Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; pgsql-performance
Gregory Stark wrote:
> "Mark Kirkwood" <markir@paradise.net.nz> writes:
>
>
>> I spent today looking at getting this patch into a self contained state.
>> Working against HEAD I'm getting bogged down in the PathKeyItem to
>> PathKey/EquivalenceClass/EquivalenceMember(s) change. So I figured I'd divide
>> and conquer to some extent, and initially provide a patch:
>>
>> - against 8.2.(5)
>> - self contained (i.e no mystery functions)
>>
>
> That would be helpful for me. It would include the bits I'm looking for.
>
>
>> The next step would be to update to to HEAD. That would hopefully provide some
>> useful material for others working on this.
>>
>
> If that's not too much work then that would be great but if it's a lot of work
> then it may not be worth it if I'm planning to only take certain bits. On the
> other hand if it's good then we might just want to take it wholesale and then
> add to it.
>
>
Here is a (somewhat hurried) self-contained version of the patch under
discussion. It applies to 8.2.5 and the resultant code compiles and
runs. I've left in some unneeded parallel stuff (PathLocus struct),
which I can weed out in a subsequent version if desired. I also removed
the 'cdb ' from most of the function names and (I hope) any Greenplum
copyrights.
I discovered that the patch solves a slightly different problem... it
pulls up index scans as a viable path choice, (but not for the DESC
case) but does not push down the LIMIT to the child tables ... so the
actual performance improvement is zero - however hopefully the patch
provides useful raw material to help.
e.g - using the examine schema from the OP email - but removing the DESC
from the query:
part=# set enable_seqscan=off;
SET
part=# explain SELECT * FROM n_traf ORDER BY date_time LIMIT 1;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=198367.14..198367.15 rows=1 width=20)
-> Sort (cost=198367.14..200870.92 rows=1001510 width=20)
Sort Key: public.n_traf.date_time
-> Result (cost=0.00..57464.92 rows=1001510 width=20)
-> Append (cost=0.00..57464.92 rows=1001510 width=20)
-> Index Scan using n_traf_date_time_login_id on
n_traf (cost=0.00..66.90 rows=1510 width=20)
-> Index Scan using
n_traf_y2007m01_date_time_login_id on n_traf_y2007m01 n_traf
(cost=0.00..4748.38 rows=83043 width=20)
-> Index Scan using
n_traf_y2007m02_date_time_login_id on n_traf_y2007m02 n_traf
(cost=0.00..4772.60 rows=83274 width=20)
-> Index Scan using
n_traf_y2007m03_date_time_login_id on n_traf_y2007m03 n_traf
(cost=0.00..4782.12 rows=83330 width=20)
-> Index Scan using
n_traf_y2007m04_date_time_login_id on n_traf_y2007m04 n_traf
(cost=0.00..4818.29 rows=83609 width=20)
-> Index Scan using
n_traf_y2007m05_date_time_login_id on n_traf_y2007m05 n_traf
(cost=0.00..4721.85 rows=82830 width=20)
-> Index Scan using
n_traf_y2007m06_date_time_login_id on n_traf_y2007m06 n_traf
(cost=0.00..4766.56 rows=83357 width=20)
-> Index Scan using
n_traf_y2007m07_date_time_login_id on n_traf_y2007m07 n_traf
(cost=0.00..4800.44 rows=83548 width=20)
-> Index Scan using
n_traf_y2007m08_date_time_login_id on n_traf_y2007m08 n_traf
(cost=0.00..4787.55 rows=83248 width=20)
-> Index Scan using
n_traf_y2007m09_date_time_login_id on n_traf_y2007m09 n_traf
(cost=0.00..4830.67 rows=83389 width=20)
-> Index Scan using
n_traf_y2007m10_date_time_login_id on n_traf_y2007m10 n_traf
(cost=0.00..4795.78 rows=82993 width=20)
-> Index Scan using
n_traf_y2007m11_date_time_login_id on n_traf_y2007m11 n_traf
(cost=0.00..4754.26 rows=83351 width=20)
-> Index Scan using
n_traf_y2007m12_date_time_login_id on n_traf_y2007m12 n_traf
(cost=0.00..4819.51 rows=84028 width=20)
(18 rows)
Attachments:
index-order-by-wip.patch.gz (application/x-gzip, 11.0K) download
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-11-08 06:21 Gregory Stark <stark@enterprisedb.com>
parent: Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 1 reply; 33+ messages in thread
From: Gregory Stark @ 2007-11-08 06:21 UTC (permalink / raw)
To: Mark Kirkwood <markir@paradise.net.nz>; +Cc: Luke Lonergan <llonergan@greenplum.com>; Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; pgsql-performance
"Mark Kirkwood" <markir@paradise.net.nz> writes:
> Here is a (somewhat hurried) self-contained version of the patch under
> discussion. It applies to 8.2.5 and the resultant code compiles and runs. I've
> left in some unneeded parallel stuff (PathLocus struct), which I can weed out
> in a subsequent version if desired. I also removed the 'cdb ' from most of the
> function names and (I hope) any Greenplum copyrights.
Thanks, I'll take a look at it.
> I discovered that the patch solves a slightly different problem... it pulls up
> index scans as a viable path choice, (but not for the DESC case) but does not
> push down the LIMIT to the child tables ... so the actual performance
> improvement is zero - however hopefully the patch provides useful raw material
> to help.
> SET
> part=# explain SELECT * FROM n_traf ORDER BY date_time LIMIT 1;
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=198367.14..198367.15 rows=1 width=20)
> -> Sort (cost=198367.14..200870.92 rows=1001510 width=20)
> Sort Key: public.n_traf.date_time
> -> Result (cost=0.00..57464.92 rows=1001510 width=20)
> -> Append (cost=0.00..57464.92 rows=1001510 width=20)
> -> Index Scan using n_traf_date_time_login_id on n_traf
> (cost=0.00..66.90 rows=1510 width=20)
That looks suspicious. There's likely no good reason to be using the index
scan unless it avoids the sort node above the Append node. That's what I hope
to do by having the Append executor code do what's necessary to maintain the
order.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-11-08 06:40 Luke Lonergan <llonergan@greenplum.com>
parent: Gregory Stark <stark@enterprisedb.com>
0 siblings, 1 reply; 33+ messages in thread
From: Luke Lonergan @ 2007-11-08 06:40 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; Mark Kirkwood <markir@paradise.net.nz>; +Cc: Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; pgsql-performance
On 11/7/07 10:21 PM, "Gregory Stark" <stark@enterprisedb.com> wrote:
>> part=# explain SELECT * FROM n_traf ORDER BY date_time LIMIT 1;
>> QUERY PLAN
>> -----------------------------------------------------------------------------
>> --------------------------------------------------------------------
>> Limit (cost=198367.14..198367.15 rows=1 width=20)
>> -> Sort (cost=198367.14..200870.92 rows=1001510 width=20)
>> Sort Key: public.n_traf.date_time
>> -> Result (cost=0.00..57464.92 rows=1001510 width=20)
>> -> Append (cost=0.00..57464.92 rows=1001510 width=20)
>> -> Index Scan using n_traf_date_time_login_id on n_traf
>> (cost=0.00..66.90 rows=1510 width=20)
>
> That looks suspicious. There's likely no good reason to be using the index
> scan unless it avoids the sort node above the Append node. That's what I hope
> to do by having the Append executor code do what's necessary to maintain the
> order.
Yah - the way it works in GPDB is that you get a non-sorting plan with an
index scan below the parent - that was the point of the fix. Hmm.
- Luke
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2007-11-08 07:18 Mark Kirkwood <markir@paradise.net.nz>
parent: Luke Lonergan <llonergan@greenplum.com>
0 siblings, 0 replies; 33+ messages in thread
From: Mark Kirkwood @ 2007-11-08 07:18 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: Gregory Stark <stark@enterprisedb.com>; Heikki Linnakangas <heikki@enterprisedb.com>; Anton <anton200@gmail.com>; pgsql-performance
Luke Lonergan wrote:
> On 11/7/07 10:21 PM, "Gregory Stark" <stark@enterprisedb.com> wrote:
>
>
>>> part=# explain SELECT * FROM n_traf ORDER BY date_time LIMIT 1;
>>> QUERY PLAN
>>> -----------------------------------------------------------------------------
>>> --------------------------------------------------------------------
>>> Limit (cost=198367.14..198367.15 rows=1 width=20)
>>> -> Sort (cost=198367.14..200870.92 rows=1001510 width=20)
>>> Sort Key: public.n_traf.date_time
>>> -> Result (cost=0.00..57464.92 rows=1001510 width=20)
>>> -> Append (cost=0.00..57464.92 rows=1001510 width=20)
>>> -> Index Scan using n_traf_date_time_login_id on n_traf
>>> (cost=0.00..66.90 rows=1510 width=20)
>>>
>> That looks suspicious. There's likely no good reason to be using the index
>> scan unless it avoids the sort node above the Append node. That's what I hope
>> to do by having the Append executor code do what's necessary to maintain the
>> order.
>>
>
> Yah - the way it works in GPDB is that you get a non-sorting plan with an
> index scan below the parent - that was the point of the fix. Hmm.
>
>
Unfortunately our plan in GPDB looks exactly the same in this case - so
we have a bit of work to do as well! Initially I wondered if I have got
something wrong in the patch... and checked on GPDB - only to see the
same behaviour! (see prev comment about LIMIT).
Cheers
Mark
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
@ 2008-03-12 19:16 Bruce Momjian <bruce@momjian.us>
parent: Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 0 replies; 33+ messages in thread
From: Bruce Momjian @ 2008-03-12 19:16 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: Luke Lonergan <llonergan@greenplum.com>; Anton <anton200@gmail.com>; pgsql-performance
Heikki Linnakangas wrote:
> Bruce, would you please add this to the 8.4 patch queue so we remember
> to look at this later?
OK, added to queue, but Tom's patch queue comment is:
This is useless since it does not represent a complete patch; the
provided code calls a lot of Greenplum-private routines that weren't
provided. It's not even reviewable let alone a candidate to apply.
---------------------------------------------------------------------------
>
> It didn't occur to me that we can do that in the degenerate case when
> there's just a single node below the Append. A more general solution
> would be to check if the pathkeys of all the child nodes match, and do a
> "merge append" similar to a merge join.
>
> Luke Lonergan wrote:
> > Below is a patch against 8.2.4 (more or less), Heikki can you take a look at
> > it?
> >
> > This enables the use of index scan of a child table by recognizing sort
> > order of the append node. Kurt Harriman did the work.
> >
> > - Luke
> >
> > Index: cdb-pg/src/backend/optimizer/path/indxpath.c
> > ===================================================================
> > RCS file:
> > /data/FISHEYE_REPOSITORIES/greenplum/cvsroot/cdb2/cdb-pg/src/backend/optimiz
> > er/path/indxpath.c,v
> > diff -u -N -r1.22 -r1.22.2.1
> > --- cdb-pg/src/backend/optimizer/path/indxpath.c 25 Apr 2007 22:07:21
> > -0000 1.22
> > +++ cdb-pg/src/backend/optimizer/path/indxpath.c 10 Aug 2007 03:41:15
> > -0000 1.22.2.1
> > @@ -379,8 +379,51 @@
> > index_pathkeys = build_index_pathkeys(root, index,
> > ForwardScanDirection,
> > true);
> > - useful_pathkeys = truncate_useless_pathkeys(root, rel,
> > - index_pathkeys);
> > + /*
> > + * CDB: For appendrel child, pathkeys contain Var nodes in
> > terms
> > + * of the child's baserel. Transform the pathkey list to refer
> > to
> > + * columns of the appendrel.
> > + */
> > + if (rel->reloptkind == RELOPT_OTHER_MEMBER_REL)
> > + {
> > + AppendRelInfo *appinfo = NULL;
> > + RelOptInfo *appendrel = NULL;
> > + ListCell *appcell;
> > + CdbPathLocus notalocus;
> > +
> > + /* Find the appendrel of which this baserel is a child. */
> > + foreach(appcell, root->append_rel_list)
> > + {
> > + appinfo = (AppendRelInfo *)lfirst(appcell);
> > + if (appinfo->child_relid == rel->relid)
> > + break;
> > + }
> > + Assert(appinfo);
> > + appendrel = find_base_rel(root, appinfo->parent_relid);
> > +
> > + /*
> > + * The pathkey list happens to have the same format as the
> > + * partitioning key of a Hashed locus, so by disguising it
> > + * we can use cdbpathlocus_pull_above_projection() to do
> > the
> > + * transformation.
> > + */
> > + CdbPathLocus_MakeHashed(¬alocus, index_pathkeys);
> > + notalocus =
> > + cdbpathlocus_pull_above_projection(root,
> > + notalocus,
> > + rel->relids,
> > + rel->reltargetlist,
> > +
> > appendrel->reltargetlist,
> > + appendrel->relid);
> > + if (CdbPathLocus_IsHashed(notalocus))
> > + index_pathkeys = truncate_useless_pathkeys(root,
> > appendrel,
> > +
> > notalocus.partkey);
> > + else
> > + index_pathkeys = NULL;
> > + }
> > +
> > + useful_pathkeys = truncate_useless_pathkeys(root, rel,
> > + index_pathkeys);
> > }
> > else
> > useful_pathkeys = NIL;
> > Index: cdb-pg/src/backend/optimizer/path/pathkeys.c
> > ===================================================================
> > RCS file:
> > /data/FISHEYE_REPOSITORIES/greenplum/cvsroot/cdb2/cdb-pg/src/backend/optimiz
> > er/path/pathkeys.c,v
> > diff -u -N -r1.18 -r1.18.2.1
> > --- cdb-pg/src/backend/optimizer/path/pathkeys.c 30 Apr 2007 05:44:07
> > -0000 1.18
> > +++ cdb-pg/src/backend/optimizer/path/pathkeys.c 10 Aug 2007 03:41:15
> > -0000 1.18.2.1
> > @@ -1403,55 +1403,53 @@
> > {
> > PathKeyItem *item;
> > Expr *newexpr;
> > + AttrNumber targetindex;
> >
> > Assert(pathkey);
> >
> > - /* Use constant expr if available. Will be at head of list. */
> > - if (CdbPathkeyEqualsConstant(pathkey))
> > + /* Find an expr that we can rewrite to use the projected columns. */
> > + item = cdbpullup_findPathKeyItemInTargetList(pathkey,
> > + relids,
> > + targetlist,
> > + &targetindex); // OUT
> > +
> > + /* If not found, see if the equiv class contains a constant expr. */
> > + if (!item &&
> > + CdbPathkeyEqualsConstant(pathkey))
> > {
> > item = (PathKeyItem *)linitial(pathkey);
> > newexpr = (Expr *)copyObject(item->key);
> > }
> >
> > - /* New vars for old! */
> > - else
> > - {
> > - AttrNumber targetindex;
> > + /* Fail if no usable expr. */
> > + else if (!item)
> > + return NULL;
> >
> > - /* Find an expr that we can rewrite to use the projected columns.
> > */
> > - item = cdbpullup_findPathKeyItemInTargetList(pathkey,
> > - relids,
> > - targetlist,
> > - &targetindex); // OUT
> > - if (!item)
> > - return NULL;
> > + /* If found matching targetlist item, make a Var that references it. */
> > + else if (targetindex > 0)
> > + newexpr = (Expr *)cdbpullup_makeVar(newrelid,
> > + targetindex,
> > + newvarlist,
> > + (Expr *)item->key);
> >
> > - /* If found matching targetlist item, make a Var that references
> > it. */
> > - if (targetindex > 0)
> > - newexpr = (Expr *)cdbpullup_makeVar(newrelid,
> > - targetindex,
> > - newvarlist,
> > - (Expr *)item->key);
> > + /* Replace expr's Var nodes with new ones referencing the targetlist.
> > */
> > + else
> > + newexpr = cdbpullup_expr((Expr *)item->key,
> > + targetlist,
> > + newvarlist,
> > + newrelid);
> >
> > - /* Replace expr's Var nodes with new ones referencing the
> > targetlist. */
> > - else
> > - newexpr = cdbpullup_expr((Expr *)item->key,
> > - targetlist,
> > - newvarlist,
> > - newrelid);
> > + /* Pull up RelabelType node too, unless tlist expr has right type. */
> > + if (IsA(item->key, RelabelType))
> > + {
> > + RelabelType *oldrelabel = (RelabelType *)item->key;
> >
> > - /* Pull up RelabelType node too, unless tlist expr has right type.
> > */
> > - if (IsA(item->key, RelabelType))
> > - {
> > - RelabelType *oldrelabel = (RelabelType *)item->key;
> > -
> > - if (oldrelabel->resulttype != exprType((Node *)newexpr) ||
> > - oldrelabel->resulttypmod != exprTypmod((Node *)newexpr))
> > - newexpr = (Expr *)makeRelabelType(newexpr,
> > - oldrelabel->resulttype,
> > - oldrelabel->resulttypmod,
> > -
> > oldrelabel->relabelformat);
> > - }
> > + if (oldrelabel->resulttype != exprType((Node *)newexpr) ||
> > + oldrelabel->resulttypmod != exprTypmod((Node *)newexpr))
> > + newexpr = (Expr *)makeRelabelType(newexpr,
> > + oldrelabel->resulttype,
> > + oldrelabel->resulttypmod,
> > + oldrelabel->relabelformat);
> > }
> > Insist(newexpr);
> >
> > Index: cdb-pg/src/backend/optimizer/util/pathnode.c
> > ===================================================================
> > RCS file:
> > /data/FISHEYE_REPOSITORIES/greenplum/cvsroot/cdb2/cdb-pg/src/backend/optimiz
> > er/util/pathnode.c,v
> > diff -u -N -r1.52.2.4 -r1.52.2.5
> > --- cdb-pg/src/backend/optimizer/util/pathnode.c 5 Aug 2007 23:06:44
> > -0000 1.52.2.4
> > +++ cdb-pg/src/backend/optimizer/util/pathnode.c 10 Aug 2007 03:41:15
> > -0000 1.52.2.5
> > @@ -1563,7 +1563,15 @@
> > pathnode->path.rescannable = false;
> > }
> >
> > - return pathnode;
> > + /*
> > + * CDB: If there is exactly one subpath, its ordering is preserved.
> > + * Child rel's pathkey exprs are already expressed in terms of the
> > + * columns of the parent appendrel. See find_usable_indexes().
> > + */
> > + if (list_length(subpaths) == 1)
> > + pathnode->path.pathkeys = ((Path *)linitial(subpaths))->pathkeys;
> > +
> > + return pathnode;
> > }
> >
> > /*
> >
> >
> > On 8/24/07 3:38 AM, "Heikki Linnakangas" <heikki@enterprisedb.com> wrote:
> >
> >> Anton wrote:
> >>>>> =# 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_y2007m12 n_traf
> >>>>> (cost=0.00..22.30 rows=1230 width=32)
> >>>>> (18 rows)
> >>>>>
> >>>>> Why it no uses indexes at all?
> >>>>> -------------------------------------------
> >>>> I'm no expert but I'd guess that the the planner doesn't know which
> >>>> partition holds the latest time so it has to read them all.
> >>> Agree. But why it not uses indexes when it reading them?
> >> The planner isn't smart enough to push the "ORDER BY ... LIMIT ..."
> >> below the append node. Therefore it needs to fetch all rows from all the
> >> tables, and the fastest way to do that is a seq scan.
> >
> >
>
>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 33+ messages in thread
end of thread, other threads:[~2008-03-12 19:16 UTC | newest]
Thread overview: 33+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-24 08:53 partitioned table and ORDER BY indexed_field DESC LIMIT 1 Anton <anton200@gmail.com>
2007-08-24 10:24 ` Mikko Partio <mpartio@gmail.com>
2007-08-24 10:32 ` Anton <anton200@gmail.com>
2007-08-24 10:38 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-08-24 16:20 ` Luke Lonergan <llonergan@greenplum.com>
2007-08-24 16:25 ` Luke Lonergan <llonergan@greenplum.com>
2007-08-28 10:20 ` Heikki Linnakangas <heikki@enterprisedb.com>
2008-03-12 19:16 ` Bruce Momjian <bruce@momjian.us>
2007-10-27 04:26 ` Anton <anton200@gmail.com>
2007-10-27 05:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-27 08:53 ` Anton <anton200@gmail.com>
2007-10-27 09:11 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-08-27 12:57 ` Tomas Tamosaitis <tomas.tamosaitis@connecty.lt>
2007-10-27 19:12 Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1 Luke Lonergan <LLonergan@greenplum.com>
2007-10-27 21:31 ` Simon Riggs <simon@2ndquadrant.com>
2007-10-29 13:40 ` Gregory Stark <stark@enterprisedb.com>
2007-10-30 05:06 ` Luke Lonergan <llonergan@greenplum.com>
2007-10-30 05:46 ` Mark Kirkwood <markir@paradise.net.nz>
2007-10-30 09:06 ` Gregory Stark <stark@enterprisedb.com>
2007-11-05 05:49 ` Mark Kirkwood <markir@paradise.net.nz>
2007-11-05 12:12 ` Gregory Stark <stark@enterprisedb.com>
2007-11-08 02:46 ` Mark Kirkwood <markir@paradise.net.nz>
2007-11-08 06:21 ` Gregory Stark <stark@enterprisedb.com>
2007-11-08 06:40 ` Luke Lonergan <llonergan@greenplum.com>
2007-11-08 07:18 ` Mark Kirkwood <markir@paradise.net.nz>
2007-10-31 14:16 ` Luke Lonergan <llonergan@greenplum.com>
2007-10-30 05:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-27 19:28 Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1 Luke Lonergan <LLonergan@greenplum.com>
2007-10-28 00:20 ` Gregory Stark <stark@enterprisedb.com>
2007-10-27 21:48 Re: partitioned table and ORDER BY indexed_field DESCLIMIT 1 Luke Lonergan <LLonergan@greenplum.com>
2007-10-28 09:13 ` Re: partitioned table and ORDER BY indexed_field DESCLIMIT 1 Simon Riggs <simon@2ndquadrant.com>
2007-10-28 16:53 ` Re: partitioned table and ORDER BY indexed_field DESCLIMIT 1 Tom Lane <tgl@sss.pgh.pa.us>
2007-10-28 17:27 ` Re: partitioned table and ORDER BY indexed_field DESCLIMIT 1 Simon Riggs <simon@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox