Received: from serlin.ivsol (IDENT:root@[148.233.222.76]) by hub.org (8.10.1/8.10.1) with ESMTP id e7KM3Wm28047 for ; Sun, 20 Aug 2000 18:03:33 -0400 (EDT) Received: from fralet (fralet.ivsol [192.168.1.11]) by serlin.ivsol (8.9.3/8.8.7) with SMTP id RAA00949; Sun, 20 Aug 2000 17:05:43 -0500 Message-ID: <002f01c00af3$4efce0a0$0b01a8c0@ivsol> From: "Franz J Fortuny" To: "Tom Lane" Cc: "pgSQL" References: <010501c00ac0$72d47a20$0b01a8c0@ivsol> <10681.966794490@sss.pgh.pa.us> Subject: RE: Speed or configuration Date: Sun, 20 Aug 2000 17:09:28 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 X-Archive-Number: 200008/197 "What version of Postgres are you using, and what does EXPLAIN show as the query plan for this query? How many tableY rows is the sub- query likely to produce, and how many matches do you expect to get from tableX?" Version: postgresql-7.0.2-2.i386.rpm Explain: Scan table, scan table. (Plus the costs, etc.) About 9,000 matches from tableX are expected, and a lot less from tableY (about 2000, at the most). The index structure of tableY includes 5 fields. Most of the time, we need only the first and second fields. Less often, up to the third field and a lot less often the other 2 left fields (in the "where" clauses). However, field1 of tableX references field1 of tableY. If I use a program to get the results, they come out incredibly fast (faster in postgreSQL than with the commercial program) (A select to get a first record set from tableY, then navigate the rows, executing a select for tableX for each row...) These the structures of the tables: create table tableX ( col0 integer not null primary key, col1 integer not null, col2 integer not null, col3 char(20), col4 char(8), col5 char(8), unique(col1,col2,col3,col4,col5) ); create table tableY ( col0 integer not null references tableX(col0), col1 integer, col2 integer, col3 float, col4 float ); The slow query is: select col1,col2,sum(col3),sum(col4) from tableY where col0 in (select col0 from tableX where col1=:col1 and col2=:col2) group by col1,col2; Explain plan shows ALWAYS scan table, scan table, scan table. tableY contains about 900,000 rows, and tableX about 65,000. Any more light about the slowness? Franz J Fortuny