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