I have two tables, staff (530 rows) and location (2.5 million rows). I
do a query that joins the two together, as so:
SELECT s.ProprietorId, l.LocationId, s.RoleId
FROM Location l
INNER JOIN (
SELECT *
FROM Staff
) s ON l.ProprietorId = s.
Colin McGuigan <[EMAIL PROTECTED]> writes:
> I know I can do it by adjusting cost parameters, but I was really
> curious as to why adding a "LIMIT 5000" onto a SELECT from a table with
> only 530 rows in it would affect matters at all.
The LIMIT prevents the sub-select from being flattened into
Tom Lane wrote:
The right way to do it is to adjust the planner cost parameters.
The standard values of those are set on the assumption of
tables-much-bigger-than-memory, a situation in which the planner's
preferred plan probably would be the best. What you are testing here
is most likely a situ
Colin McGuigan <[EMAIL PROTECTED]> writes:
> -> Subquery Scan s (cost=0.00..21.93 rows=1 width=8)
> Filter: ((userid = 123456) AND (locationid IS NULL))
> -> Limit (cost=0.00..15.30 rows=530 width=102)
> -> Seq Scan on staff (cost=0.00..15.30 rows=530 width=102
I have two tables, staff (530 rows) and location (2.5 million rows). I
do a query that joins the two together, as so:
SELECT s.ProprietorId, l.LocationId, s.RoleId
FROM Location l
INNER JOIN (
SELECT *
FROM Staff
) s ON l.ProprietorId = s.ProprietorId
WHERE s.UserId =
