From: Stoyan Genov <genov@digsys.bg>
To: pgsql-sql@postgresql.org
Cc: wieck@devis.com
Subject: Re: [SQL] Optimizing a query through its syntax and indices
Date: Thu, 04 Nov 1999 14:59:03 +0200
Message-ID: <199911041259.OAA01142@lorna.digsys.bg> (raw)
>>
> > 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: genov@digsys.bg, wieck@devis.com
Subject: Re: [SQL] Optimizing a query through its syntax and indices
In-Reply-To: <199911041259.OAA01142@lorna.digsys.bg>
References: <199911041259.OAA01142@lorna.digsys.bg>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox