Re: Performance improvement hints

On Tue, Sep 12, 2000 at 02:30:09PM +0200, devik(at)cdi(dot)cz wrote:
> Hello,
> I have encountered problems with particular query so that
> a started to dug into sources. I've two questions/ideas:
>
> 1) when optimizer computes size of join it does it as
> card(R1)*card(R2)*selectivity. Suppose two relations
> (R1 & R2) each 10000 rows. If you (inner) join them
> using equality operator, the result is at most 10000
> rows (min(card(R1),card(R2)). But pg estimates
> 1 000 000 (uses selectivity 0.01 here).

Surely not. If you inner join, you can get many more than min
(card(R1),card(R2)), if you are joining over non-unique keys (a common
case). For example:

employee:

name job

Jon Programmer
George Programmer

job_drinks

job drink

Programmer Jolt
Programmer Coffee
Programmer Beer

The natural (inner) join between these two tables results in 6 rows,
card(R1)*card(R2).

I think you mean that min(card(R1),card(R2)) is the correct figure
when the join is done over a unique key in both tables.

>
> 2) suppose we have relation R1(id,name) and index ix(id,name)
> on it. In query like: select id,name from R1 order by id
> planner will prefer to do seqscan+sort (althought the R1
> is rather big). And yes it is really faster than using
> indexscan.
> But indexscan always lookups actual record in heap even if
> all needed attributes are contained in the index.
> Oracle and even MSSQL reads attributes directly from index
> without looking for actual tuple at heap.
> Is there any need to do it in such ineffecient way ?

I believe this is because PgSQL doesn't remove entries from the index
at DELETE time, thus it is always necessary to refer to the main table
in case the entry found in the index has since been deleted.
Presumably this speeds up deletes (but I find this behaviour suprising
too).

Jules