Re: Improving Inner Join Performance

On 1/6/06, Andy <frum(at)ar-sd(dot)net> wrote:
> At the moment: o.id_status>3 can have values only 4 and 6. The 6 is around
> 90% from the whole table. This is why seq scan is made.
>
given this if you make id_status > 3 you will never use an index
because you will be scanning 4 and 6 the only values in this field as
you say, and even if there were any other value 6 is 90% of whole
table, so an index for this will not be used...

> Now, depending on the user input the query can have more where fields. For
> example:
> SELECT count(*) FROM orders o
> INNER JOIN report r ON r.id_order=o.id
> WHERE o.id_status > 3 AND r.id_zufriden=7
>
here the planner can be more selective, and of course the query is
faster... if you will be loading data load it all then make tests...

but because your actual data the planner will always choose to scan
the entire orders table for o.id_status > 3...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)