Re: [HACKERS] All things equal, we are still alot slower then MySQL?

On Sun, 19 Sep 1999, Tom Lane wrote:

> How many tuples *does* your test query produce, anyway? If you

Depends on what it is fed...could be 270 records returned, could be
5...depends on the values of catid, indid and divid...

> eliminate all the joining WHERE-clauses and just consider the
> restriction clauses for each of the tables, how many tuples?
> In other words, what do you get from
>
> SELECT count(*)
> FROM aecEntMain a
> WHERE (a.id=??? AND a.mid=???)
> AND (a.status like 'active%')
> AND (a.status like '%active:ALL%')
> AND (a.representation like '%:ALL%');

Returns 1 ...

> SELECT count(*)
> FROM aecWebEntry b
> WHERE (b.status like 'active%')
> AND (b.status like '%active:ALL%')
> AND (b.indid=? and b.divid=? and b.catid=?);

This one I get 39 ...

> (In the first of these, substitute a representative id/mid pair from
> table b for the ???, to simulate what will happen in any one iteration
> of the inner scan over table a.) Also, how many rows in each table?

aec=> select count(*) from aecEntMain;
count
-----
16560
(1 row)

aec=> select count(*) from aecWebEntry;
count
-----
58316
(1 row)

By doing a 'select distinct id from aecWebEntry', there are 16416 distinct
id's in aecWebEntry, and 16493 distinct id's in aecEntMain, so I'm
guessing that its supposed to be a 1->N relationship between the two
tables...therefore, again, I'm guessing, but the first query above shoudl
never return more then 1 record...

If I run both queries together, as:
SELECT distinct b.indid, b.divid, b.catid, a.id, a.mid
FROM aecEntMain a, aecWebEntry b
WHERE (a.id=b.id AND a.mid=b.mid)
AND (a.status like 'active%' and b.status like 'active%')
AND (a.status like '%active:ALL%' and b.status like '%active:ALL%')
AND (a.representation like '%:ALL%')
AND (b.indid='000001' and b.divid='100016' and b.catid='100300');

The result, in this case, is 39 records...if I change b.catid to be '100400',
its only 35 records, etc...

Does this help? The server isn't live, so if you want me to enable some
debugging, or play with something, its not going to affect anything...

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org