2

Faced with need of using column aliases in where condition for selection. Found possible solution here.

Let's assume we have one-to-one relationship (user-to-role) and we want to get results as following:

SELECT u.name AS u_name, r.name AS r_name
FROM users AS u
  INNER JOIN roles AS r
    ON u.role_id = r.role_id
WHERE u.name = 'John'

And we have corresponding idex for user.name (just for example).

If this query is run with EXPLAIN, it shows all indexes that are used during selection (including index for name).

Now, as we want to use aliases in WHERE clause, based on proposed solution we can rewrite the query:

SELECT * FROM (
  SELECT u.name AS u_name, r.name AS r_name
  FROM users AS u
    INNER JOIN roles AS r
      ON u.role_id = r.role_id
) AS temp
WHERE u_name = 'John'

As you see, there's no WHERE clause in nested select. Running this query with EXPLAIN gives the same results (just to admit, I'm not an expert in analyzing results of 'explain', but still):

  • same indexes
  • same costs
  • similar time of execution

And I'm a little bit confused by this result: was convinced that at least index for user name won't be used.

Q1: Does postgres use indexes in that way?

Q2: Are there possible performance issues?

Dmitry Dyokin
  • 139
  • 11
  • 3
    The optimizer can rewrite the query and that way find better ways of solving the problem. Usually it does the right thing and you don't need to worry. – Laurenz Albe May 19 '18 at 15:02

2 Answers2

5

The subquery is not needed, so it can be unrolled/collapsed.

The following query will generate a flat plan (and indexes are not relevant) 

\i tmp.sql

CREATE TABLE t
        (a integer not null primary key
        );

insert into t(a)
select v from generate_series(1,10000) v
        ;

ANALYZE t;

EXPLAIN
SELECT * from (
        select d AS e from (
                select c as d from (
                        select b AS c from (
                                select a AS b from t
                                        ) s
                                ) r
                        ) q
                ) p
where e =95
        ;

Resulting plan:

DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 10000
ANALYZE
                             QUERY PLAN                              
---------------------------------------------------------------------
 Index Only Scan using t_pkey on t  (cost=0.17..2.38 rows=1 width=4)
   Index Cond: (a = 95)
(2 rows)

In the OP's fragment,the innermost query (table expression) is a two-table join , but the mechanism is the same: all the outer layers can be peeled off (and the result column is renamed)

And yes: the join will benefit from indexes on the joined fields, and the final where could use an index, too.

wildplasser
  • 43,142
  • 8
  • 66
  • 109
3

SQL is a descriptive language, not a procedural language. A SQL query describes the result set being produced. It does not specify how to create it -- and that is even more true in Postgres which doesn't have compiler options or hints.

What actually gets run is a directed acyclic graph of operations (DAG). The compiling step creates the DAG. Postgres is smart enough to realize that the subquery is meaningless, so the two versions are optimized to the same DAG.

Let me add that I think Postgres usually materializes CTEs, so using a CTE might prevent the index from being used.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786