[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Gnumed-devel] re: optimizations for inheritance searching problem.
From: |
Karsten Hilbert |
Subject: |
Re: [Gnumed-devel] re: optimizations for inheritance searching problem. |
Date: |
Fri, 10 Mar 2006 19:24:24 +0100 |
User-agent: |
Mutt/1.5.11+cvs20060126 |
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