I experience a weird behavior and would like to have your opinion on it.
Given two tables, users and user_role, I’d like ho have all roles, a certain user is in (could be quite a lot because of the design of the system which does not matter here).
Table users consist of pkuserid and username (beside others) and table user_role contains only fkuserid referencing users table and pkroleid referencing the roles (in a third table that is out of interest here). Furthermore I have an index on users.username and on user_role.fkuserid.
I experienced quite long query times and investigated further.
Having the two tables joined takes approx 5 seconds!
select ur.fkuserid, ur.fkroleid from user_role ur, users u where ur.fkuserid = u.pkuserid and u.username = 'username' limit 10000;
as well as having a subselect.
select ur.fkuserid, ur.fkroleid from user_role@active_user_role ur where ur.fkuserid = (select u.pkuserid from users u where u.username = 'username') limit 10000;
However, when I avoid the join by previously acquiring the user’s primary key it only takes 5ms!
select ur.fkuserid, ur.fkroleid from user_role ur where ur.fkuserid = 123456 limit 10000;
The user_role table is quite big: approx. 2 Mio entries and the users table has about 170.000 rows.
It seems to me that the subquery and the join expression gets evaluated every time for each hit in the user_role table.
Query plan: is
Level |Type |Field |Description | ------|-------|-----------|----------------------------| 0 |limit | | | 1 |render | | | 2 |scan | | | 2 | |table |user_role@active_user_role | 2 | |spans |ALL | 2 | |subqueries |1 | 3 |limit | | | 4 |render | | | 5 |scan | | | 5 | |table |users@users_username_key | 5 | |spans |/"username"-/"username\x00" |