CTE vs Subquery

From: Linos <info(at)linos(dot)es>
To: pgsql-performance(at)postgresql(dot)org
Subject: CTE vs Subquery
Date: 2011-10-25 16:22:42
Message-ID: 4EA6E252.6030002@linos.es
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi all,
i am having any problems with performance of queries that uses CTE, can the
join on a CTE use the index of the original table?, suppose two simple tables:

CREATE TABLE employee
(
emp_id integer NOT NULL,
name character varying NOT NULL,
CONSTRAINT employee_pkey PRIMARY KEY (emp_id )
);

CREATE TABLE employee_telephone
(
emp_id integer NOT NULL,
phone_type character varying NOT NULL,
phone_number character varying NOT NULL,
CONSTRAINT employee_telephone_pkey PRIMARY KEY (emp_id , phone_type ),
CONSTRAINT employee_telephone_emp_id_fkey FOREIGN KEY (emp_id)
REFERENCES employee (emp_id)
);

and this two queries, i know this particular case don't need either a CTE or
subquery it is only an example:

WITH phones AS (SELECT emp_id,
phone_number
ORDER BY emp_id,
phone_type)
SELECT emp.emp_id,
emp.name,
array_to_string(array_agg(phones.phone_number)) AS phones

FROM employee AS emp
JOIN phones ON phones.emp_id = emp.emp_id

VS

SELECT emp.emp_id,
emp.name,
array_to_string(array_agg(phones.phone_number)) AS phones

FROM employee AS emp
JOIN (SELECT emp_id,
phone_number
ORDER BY emp_id,
phone_type) AS phones ON phones.emp_id = emp.emp_id

Why the CTE it is slower in many cases? does the CTE don't use the index for the
join and the subquery do? if the CTE it is usually slower where should be used
instead of a subquery other than recursive CTE's?

Regards,
Miguel Angel.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2011-10-25 16:43:45 Re: CTE vs Subquery
Previous Message David Boreham 2011-10-25 15:00:38 Re: Choosing between Intel 320, Intel 510 or OCZ Vertex 3 SSD for db server