Further testing has revealed that, indeed, PG 8.2 speeds up
our use of child tables !
The query in question went down from 10 minutes to *under a
second* just by running against 8.2 :-)
Now, that's some gain !
Thanks to the PostgreSQL developers.
Karsten,
GNUmed team
On Sun, Dec 10, 2006 at 09:43:35AM +0100, Karsten Hilbert wrote:
> Subject: [GENERAL] inheritance and index use (similar to UNION ALL)
> User-Agent: Mutt/1.5.13 (2006-08-11)
>
> Hi,
>
> we have a parent table root_item with a few common fields
> (one is a text field) from which a whole bunch of child
> tables derives.
>
> We need to run queries against the text field across the
> whole bunch of child tables. What naturally comes to mind is
> to run the query against root_item.text_field thereby
> catching all child table text_field values as well.
>
> However, the planner doesn't really seem to consider indices
> of the parent table. It was said that 8.2 would be making
> improvements related to this and the Release Notes do have a
> comment on planner improvements for UNION/inherited tables.
> And, yes, the data does warrant using indices over using seq
> scans. Explicitely joining the subtables one by one yields
> orders of magnitude faster results (10 minutes going down to
> 2 seconds) and uses indices.
>
> What I am wondering is:
>
> Should this really work (better) in 8.2 ?
>
> Do I need to provide more data (schema, explain plan etc) ?
>
> Am I doing something wrong (apart from perhaps chosing a
> non-performant schema design) ?
>
> Thanks,
> Karsten
> --
> GPG key ID E4071346 @ wwwkeys.pgp.net
> E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346