Re: [Gnumed-devel] re: optimizations for inheritance searching problem.

[Karsten Hilbert]

On Fri, Mar 10, 2006 at 12:43:48PM +0800, Syan Tan wrote:

Syan, thanks for your research and solution. Which part of
the code do we need to change to take advantage of it ?

> the above stops the query parser from using sequential scan
Regarding PostgreSQL this is to be taken with a grain of
salt. Stopping the query planner from using seq scans cannot
be done because the planner decides upon a variety of
factors whether to use a seq scan or idx scan. Among them:

- availability of suitable indexes
  - previous releases had problems applying multi-column
    indexes to single-column searches
- row value statistics
  - which will have to be updated after large inserts
- join key data types
  - which must be equal in older PG releases
  - which may be prone to having to cast/quote integers
    appropriately to not fall into the trap of int4 != int8

> on all the child tables
> of clin_root_items , if searching is done on the base table clin_root_item.
> this is a problem of postgresql , which is documented in the online 
> documentation,
Can you give a pointer ? What I find only talks about
queries which use "order by" on some column.  I *have* seen
indexes being used at times in parent/child queries.

> but they must have some reason for not wanting to fix it.
Not much work is done on *improving* inheritance features
since it is not used that much yet.

> by explicitly searching the child tables and then union , the query parser 
> will 
> detect existing indexes on search attributes /join conditions (e.g. 
> fk_episode, 
> fk_encounter ) and use them. so for clin_narrative where the row count is 
> 150,000 
> it will not sequentially scan 150,000 fk_episode values, but lookup the index 
> to 
> see if fk_episode value exists in the btree or hash table.

Yep, that's the plan why we have the indexes on that.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346