Message-ID: <4EA6E252.6030002@linos.es>
Date: Tue, 25 Oct 2011 18:22:42 +0200
From: Linos <info@linos.es>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:7.0.1) Gecko/20111001 Thunderbird/7.0.1
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: CTE vs Subquery
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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.