Re: Some question - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: Some question
Date
Msg-id 4BC17DD902000025000306D4@gw.wicourts.gov
Whole thread Raw
In response to Some question  (Ľubomír Varga)
List pgsql-performance
Ľ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



pgsql-performance by date:

Previous
From: Brian Cox
Date:
Subject: Re: "could not open relation..."
Next
From: Scott Marlowe
Date:
Subject: Re: [PERFORM] About “context-switching issue on Xeon” test case ?