Re: [PERFORM] partitioned table and ORDER BY indexed_field DESC LIMIT 1
2007/10/27, Tom Lane <[EMAIL PROTECTED]>: > Anton <[EMAIL PROTECTED]> 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.
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
Re: [PERFORM] Speed difference between select ... union select ... and select from partitioned_table
Pablo Alcaraz <[EMAIL PROTECTED]> writes: > These are the EXPLAIN ANALIZE: If you raise work_mem enough to let the second query use a hash aggregate (probably a few MB would do it), I think it'll be about the same speed as the first one. The reason it's not picking that on its own is the overestimate of the number of resulting groups. This is because get_variable_numdistinct is not smart about append relations. We should try to fix that sometime... regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
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:[EMAIL PROTECTED] 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
Re: [PERFORM] partitioned table and ORDER BY indexed_field DESC LIMIT 1
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:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] 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
Re: [PERFORM] Speed difference between select ... union select ... and select from partitioned_table
On Fri, 2007-10-26 at 16:37 -0400, Pablo Alcaraz wrote: > I executed 2 equivalents queries. The first one uses a union structure. > The second uses a partitioned table. The tables are the same with 30 > millions of rows each one and the returned rows are the same. > > But the union query perform faster than the partitioned query. > > My question is: why? :) The two queries are equivalent but they have different execution plans. The UNION query has explicit GROUP BY operations within it. We do not currently perform a push-down operation onto the individual partitions. This results in more data copying as well as requiring a single very large sort, rather than lots of small ones. That is probably enough to allow it to perform the sort in memory rather than on-disk, thus allowing a considerable speed-up. This is on my list of requirements for further partitioning improvements in 8.4 or beyond. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] partitioned table and ORDER BY indexed_field DESC LIMIT 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] partitioned table and ORDER BY indexed_field DESCLIMIT 1
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:[EMAIL PROTECTED] 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
Re: [PERFORM] Speed difference between select ... union select ... and select from partitioned_table
Pablo Alcaraz wrote: These are the EXPLAIN ANALIZE: If you raise work_mem enough to let the second query use a hash aggregate (probably a few MB would do it), I think it'll be about the same speed as the first one. The reason it's not picking that on its own is the overestimate of the number of resulting groups. This is because get_variable_numdistinct is not smart about append relations. We should try to fix that sometime... I re run the partitioned-query. it completed in 15996 seconds. It builded a BIG temp file: [EMAIL PROTECTED] xxx]# ls -lh pgsql-data/data/16386/pgsql_tmp/ total 2.2G -rw--- 1 postgres postgres 1.0G Oct 27 15:35 pgsql_tmp7004.0 -rw--- 1 postgres postgres 1.0G Oct 27 15:35 pgsql_tmp7004.1 -rw--- 1 postgres postgres 175M Oct 27 15:35 pgsql_tmp7004.2 work_mem=1Mb. How much do I need to raise work_mem variable? 2.2G? Regards Pablo
Re: [PERFORM] partitioned table and ORDER BY indexed_field DESC LIMIT 1
"Luke Lonergan" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
