Re: [PERFORM] partitioned table and ORDER BY indexed_field DESC LIMIT 1

2007-10-27 Thread Gregory Stark
"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

Re: [PERFORM] Speed difference between select ... union select ... and select from partitioned_table

2007-10-27 Thread Pablo Alcaraz
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

Re: [PERFORM] partitioned table and ORDER BY indexed_field DESCLIMIT 1

2007-10-27 Thread Luke Lonergan
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- F

Re: [PERFORM] partitioned table and ORDER BY indexed_field DESC LIMIT 1

2007-10-27 Thread Simon Riggs
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 tim

Re: [PERFORM] Speed difference between select ... union select ... and select from partitioned_table

2007-10-27 Thread Simon Riggs
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 perfor

Re: [PERFORM] partitioned table and ORDER BY indexed_field DESC LIMIT 1

2007-10-27 Thread Luke Lonergan
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,

Re: [PERFORM] partitioned table and ORDER BY indexed_field DESC LIMIT 1

2007-10-27 Thread Luke Lonergan
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:[EM

Re: [PERFORM] Speed difference between select ... union select ... and select from partitioned_table

2007-10-27 Thread Tom Lane
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 overesti

Re: [PERFORM] partitioned table and ORDER BY indexed_field DESC LIMIT 1

2007-10-27 Thread Heikki Linnakangas
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

Re: [PERFORM] partitioned table and ORDER BY indexed_field DESC LIMIT 1

2007-10-27 Thread Anton
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 sele