SEO

#1 (**permalink**) 04-12-2008, 03:09 AM

> > This could be a solution... but then I'm not sure how well would do
> > queries which need the first 10 records based on some criteria which

> > does not include the group id. I guess limit queries across the
union
> > of the partitions don't work too well for now, and we do
> have such queries.

You would query the parent (no union). Do you need order by's ?
Without order by it is currently no problem.

Do we push the limit down to the separate tables when we have an
appropriate
index for the order by (that could be a TODO item)?
(You need a max of "limit" rows per child in the outer order)

Or we would need to implement an efficient index merge node
for order by queries on parent (and union all's) with low limits
and an appropriate index.

Selecting the oldest x rows from a time partitioned table is a frequent
problem
we need to work around here too (Informix db).

Andreas

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

#2 (**permalink**) 04-12-2008, 03:09 AM

First of all, I base my assumptions on what I recall to have read on
this list, as I didn't try yet partitioning using inheritance. It's not
trivial to set up and I didn't have the time to play with it yet. So I
wouldn't know for sure that it won't work fine with our application, and
that will only change when I'll get a few days to experiment. The
experimentation will include the migration of existing data to the
partitioned schema, which will be probably the most difficult part of it
due to the size of the tables which need partitioning...

> You would query the parent (no union). Do you need order by's ?
> Without order by it is currently no problem.

It's clear to me that partitioning by inheritance is transparent to the
application, what worries me is that our application likely has a few
queries which will be equivalent to a union when planning, and I fear
bad performance there.

An I need order by on all queries with limit. The few exceptions where I
wouldn't need order by are when I want to delete/update chunk-wise, but
that's not supported right now... another feature I made noise about ;-)

[snip]

Cheers,
Csaba.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Re: vacuum, performance, and MVCC