Обсуждение: inheritance and index use (similar to UNION ALL)

Поиск
Список
Период
Сортировка

inheritance and index use (similar to UNION ALL)

От
Karsten Hilbert
Дата:
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

Re: inheritance and index use (similar to UNION ALL)

От
Karsten Hilbert
Дата:
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