Ľubomír Varga wrote: > SELECT * FROM t_route > WHERE t_route.route_type_fk = 1 > limit 4; This one scanned the t_route table until it found four rows that matched. It apparently didn't need to look at very many rows to find the four matches, so it was fast. > SELECT * FROM t_route > WHERE t_route.route_type_fk = > (SELECT id FROM t_route_type WHERE type = 2) > limit 4; This one came up with an id for a route type that didn't have any matches in the t_route table, so it had to scan the entire t_route table. (Based on your next query, the subquery probably returned NULL, so there might be room for some optimization here.) If you had chosen a route type with at least four matches near the start of the route table, this query would have completed quickly. > SELECT * FROM t_route, t_route_type > WHERE t_route.route_type_fk = t_route_type.id > AND type = 2 > limit 4; Since it didn't find any t_route_type row which matched, it knew there couldn't be any output from the JOIN, so it skipped the scan of the t_route table entirely. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
