CTE vs Subquery

Поиск
Список
Период
Сортировка
От Linos
Тема CTE vs Subquery
Дата
Msg-id 4EA6E252.6030002@linos.es
обсуждение исходный текст
Ответы Re: CTE vs Subquery  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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.

В списке pgsql-performance по дате отправления:

Предыдущее
От: David Boreham
Дата:
Сообщение: Re: Choosing between Intel 320, Intel 510 or OCZ Vertex 3 SSD for db server
Следующее
От: Tom Lane
Дата:
Сообщение: Re: CTE vs Subquery