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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
