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