pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Franz J Fortuny <ffortuny@ivsol.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgSQL <pgsql-sql@postgresql.org>
Subject: RE: Speed or configuration
Date: Sun, 20 Aug 2000 17:09:28 -0500
Message-ID: <002f01c00af3$4efce0a0$0b01a8c0@ivsol> (raw)
References: <010501c00ac0$72d47a20$0b01a8c0@ivsol>
	<10681.966794490@sss.pgh.pa.us>




"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




view thread (9+ messages)  latest in thread

Message-ID: <002f01c00af3$4efce0a0$0b01a8c0@ivsol>
Permalink:  ../002f01c00af3$4efce0a0$0b01a8c0@ivsol/
Also on:    postgresql.org/message-id/002f01c00af3$4efce0a0$0b01a8c0@ivsol

 · 

reply

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: ffortuny@ivsol.com, tgl@sss.pgh.pa.us
  Subject: Re: Speed or configuration
  In-Reply-To: <002f01c00af3$4efce0a0$0b01a8c0@ivsol>
  References: <010501c00ac0$72d47a20$0b01a8c0@ivsol> <10681.966794490@sss.pgh.pa.us> <002f01c00af3$4efce0a0$0b01a8c0@ivsol>

* 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