From: | Vadim Mikheev <vadim(at)krs(dot)ru> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Patrick Giagnocavo <a222(at)redrose(dot)net>, pgsql-sql(at)postgreSQL(dot)org |
Subject: | Re: [SQL] qurey plan and indices |
Date: | 1999-08-06 02:09:08 |
Message-ID: | 37AA43C4.5DDBE65F@krs.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Tom Lane wrote:
>
> Yes. The inner query must happen by sequential scan. But the outer
> query can use an index if it is in the form
> ... where (name = 'a') or (name = 'b') or (name = 'c');
> which is what "where name in ('a','b','c')" gets translated to.
>
> However, the *real* problem here is that the inner query is treated
> as a subplan, which means it is re-evaluated for each tuple scanned
> by the outer query. This is not very bright; the system ought to
> notice that the inner query does not depend on the state of the
> outer query, and do it only once. There is already a notion of
> InitPlan vs. SubPlan. I'm not sure why this case is being classified
> as a SubPlan, but it sure looks like it ought to be done as an
> InitPlan...
Not in all cases. First, to use InitPlan you would have to add
DISTINCT to subquery to avoid duplicates. Second, there is another
way of optimization - cache/hash subquery results to avoid
re-execution of subquery plan. Planner should make decision what
way to follow.
Vadim
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 1999-08-06 02:33:32 | Re: [SQL] qurey plan and indices |
Previous Message | Richard Dice | 1999-08-05 21:28:15 | subscribe |