Re: [SQL] Optimizing a query through its syntax and indices

>>
> > Hello,
> >
> > I talk PostgreSQL 6.5.2 ...
> >
> > Suppose we have some tables we join using some of their fields.
> > Suppose there are "restrictions" for the values of some (or all) of the tables'
> > fields of this kind:
> > tableM.fieldN [ NOT ] IN (constA,constB,constC),
> > tableP.fieldQ [ NOT ] IN (constD,constE,constF),
> > etc...
> > These restrictions can occur for the fields by which we join the tables, as well
> > as for fields of tables we do not use for joins.
> >
> > Are there (can there be) general ( or not so general :-) ) rules for optimizing
> > the query ( and the performance and the speed ) through the particular order we
> > put the join statements in the WHERE clause, trough mixing/ordering the join
> > parts and the restrictions ( in the means defined above ) in the values of the
> > fields in the WHERE clause, through changing the syntax ( for example, using
> > UNION or EXCEPT clauses ), or through using one- or more-than-one-field
> > indices, or through doubling some of the restrictions if they refer to a
> > field we join by ( for example, "... WHERE tableM.fieldN = tableP.fieldQ and
> > tableM.fieldN in (constA,constB)" complemented by " and tableP.fieldQ in (constA
> > ,constB)" ).
> >
>
> The last part, complementing the qualifications, is known to
> speedup the join significantly. At least for simple
> expressions that can be put down into the scan itself. This
> is because it reduces the amount of data before the join
> already. Don't know if this is true for IN expressions too,
> so you might give it a try (and report the result back to us,
> please).
>
> There had been the idea to do this automatically in a step
> between rewriting and planning. Unfortunately noone seems to
> have the time to tackle it.
>
>
> Jan

There IS speedup for IN expressions (tried it with 2 tables join,
first with 4 fields/20000 rows, second with 30 fields/22000 rows, inidces on
the joining fields, 250 values in the IN (..) clause ). The speedup is
around
10 per cent.

Stoyan