Optimize question: Why joined query slower far more than two queries?

From: "lt" <ltshadow(at)basecity(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Optimize question: Why joined query slower far more than two queries?
Date: 2000-10-10 11:52:02
Message-ID: 000c01c032b0$810400d0$3501a8c0@virlthost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

I am confusing that Postgres 7.0.2 plan for a joined query.
I have tow tables, each contains about 36000 rows. the first tables' structure likes following:
username varchar(20) primary key,
....other columns;
and the 2nd table's structure:
username varchar(20) primary key,
....other columns;

on each table I excute a query to find a user:
explain select * from users where username='lt';
and the result
Index Scan using users_username_key on users (cost=0.00..267.01 rows=306 width=142)

the 2nd:
explain select * from wquserdata where username='lt';
result:
Index Scan using wquserdata_pkey on wquserdata (cost=0.00..240.80 rows=306 width=48)

according to that, I expect it should take 267.01+240.80 to join them(btw: why rows=306?)
But the joined query takes far more time than what I expected:
explain select t0.*,t1.* from wquserdata t0, users t1 where t0.username=t1.username and t0.username='lt';
the result:
Merge Join (cost=0.00..3033.27 rows=93670 width=190)
-> Index Scan using users_username_key on users t1 (cost=0.00..2406.06 rows=30607
width=142)
-> Index Scan using wquserdata_pkey on wquserdata t0 (cost=0.00..240.80 rows=306 width=48)

but if I use this:
explain select t0.*,t1.* from wquserdata t0, users t1 where t0.username='lt' and t1.username='lt' and t0.username=t1.username;
the result is exactly what I expected:
Merge Join (cost=0.00..515.46 rows=937 width=190)
-> Index Scan using users_username_key on users t1 (cost=0.00..267.01 rows=306 width=142)
-> Index Scan using wquserdata_pkey on wquserdata t0 (cost=0.00..240.80 rows=306 width=48)

I am using a view to select, so the latest query can not be executed in my application. Is there a way to get best performence and can be used in my view?

(my view definition: Create View wqusers as SELECT t0.username, ..., t1.... FROM users t0, wquserdata t1 WHERE (t0.username = t1.username);
and I am performing query like this:
select * from wqusers where username='lt';)

Browse pgsql-admin by date

  From Date Subject
Next Message lt 2000-10-10 11:58:42 foreign key question: the backend end the connection
Previous Message Gerhard Kroder 2000-10-10 08:28:40 Hello to pgsql!