Re: Query performance problems with partitioned tables

Just cast the value in the WHERE clause:

select ts from mwdb.t_mv where zr=3622 and ts > '2006-01-01 00:00:00'
::TIMESTAMP order by ts asc limit 1;

This search only into the right partitioned tables if you build the rules
based in the ts field.

----
Neil Peter Braggio
pbraggio(at)gmail(dot)com

On 4/30/07, Richard Huxton <dev(at)archonet(dot)com> wrote:
>
> Andreas Haumer wrote:
> >
> > I think the planner could do the following:
> >
> > a) It could make a better decision in which direction to scan
> > the partitions (depending on sort order involved in the query)
> >
> > b) It could stop scanning as soon as there can not be any further
> > resulting row according to the CHECK constraints given on the tables.
> [snip]
> > Perhaps the logic to implement this is complex, but IMHO
> > it _should_ be doable (and proofable), shouldn't it?
>
> Ah, it might be do-able for some subset of cases, but is it
> cost-effective to check for in *all* cases? Don't forget the constraints
> and where clauses can be arbitrarily complex.
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>