Received: from danbo.digsys.bg (root@danbo.digsys.bg [192.92.129.10]) by hub.org (8.9.3/8.9.3) with ESMTP id HAA01802 for ; Thu, 4 Nov 1999 07:59:17 -0500 (EST) (envelope-from genov@lorna.digsys.bg) Received: from lorna.digsys.bg (root@lorna.digsys.bg [193.68.6.15]) by danbo.digsys.bg (8.9.0/8.9.0) with ESMTP id OAA03447; Thu, 4 Nov 1999 14:59:08 +0200 (EET) Received: from lorna.digsys.bg (genov@localhost.digsys.bg [127.0.0.1]) by lorna.digsys.bg (8.9.0/8.9.0) with ESMTP id OAA01142; Thu, 4 Nov 1999 14:59:06 +0200 (EET) Message-Id: <199911041259.OAA01142@lorna.digsys.bg> X-Mailer: exmh version 2.0.2 2/24/98 To: pgsql-sql@postgresql.org cc: wieck@devis.com Subject: Re: [SQL] Optimizing a query through its syntax and indices Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Date: Thu, 04 Nov 1999 14:59:03 +0200 From: Stoyan Genov Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id HAA01826 >> > > 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