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.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
