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';)
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! |