Thread: Query optimization (select single record and join)

I have the following sql:

CREATE OR REPLACE FUNCTION foo (in x integer) RETURNS float AS $$
    SELECT max(tableB.columnC)
    FROM
        tableA inner join tableB on (tableA.columnA =
tableB.columnB)
    WHERE
        tableA.columbA = x
... (additional code to select which of the many "foo" records referencing
the same row in the foreign table is the one we want)

What I am wondering is whether the database first eliminate all rows in
tableA that don't meet the criteria before performing the join, or does it
perform the join first then eliminate all records that don't meet the
criteria?

Thanks,
Orest

Orest Kozyar wrote:
> What I am wondering is whether the database first eliminate all rows in
> tableA that don't meet the criteria before performing the join, or does it
> perform the join first then eliminate all records that don't meet the
> criteria?

If you use "EXPLAIN SELECT ..." then PostgreSQL will tell you how it's
going to execute your query. If you use "EXPLAIN ANALYZE SELECT ..." it
will tell you what it actually did too. See manuals and google for
details and discussion.

Oh, and if you don't know about VACUUM and ANALYZE you'll probably want
to read up on those too.

--
   Richard Huxton
   Archonet Ltd