Cấu trúc truy vấn và thực thi truy vấn

Cải thiện hiệu năng truy vấn trong PostgreSQL

Amy McCarty

Instructor

Truy vấn con và join

-- TRUY VẤN CON
SELECT COUNT(athlete_id)
FROM athletes
WHERE country IN 
  (SELECT country FROM climate 
    WHERE temp_annual > 22)

-- JOIN
SELECT COUNT(athlete_id)
FROM athletes a
INNER JOIN climate c
  ON a.country = c.country
  AND c.temp_annual > 22

Kế hoạch truy vấn

Aggregate  ()
  ->  Hash Join  ()
        Hash Cond: (athletes.country = climate.country)
        ->  Seq Scan on athletes  ()
        ->  Hash  ()
              ->  Seq Scan on climate  ()
                    Filter: (temp_annual > '22'::numeric)

CTE và bảng tạm thời

-- CTE
WITH celsius AS 
(
  SELECT country 
  FROM climate 
  WHERE temp_annual > 22 -- Celsius
)
SELECT count(athlete_id)
FROM athletes a
INNER JOIN celsius c
  ON a.country = c.country

-- BẢNG TẠM
CREATE TEMPORARY TABLE celsius AS 
  SELECT country 
  FROM climate 
  WHERE temp_annual > 22; -- Celsius

SELECT count(athlete_id)
FROM athletes a
INNER JOIN celsius c
  ON a.country = c.country

Kế hoạch truy vấn

  Aggregate  ()
  CTE celsius
    ->  Seq Scan on climate  ()
          Filter: (temp_annual > '22'::numeric)
  ->  Hash Join  ()
        Hash Cond: (a.country_code = c.country_code)
        ->  Seq Scan on athletes a  ()
        ->  Hash  ()
              ->  CTE Scan on celsius c  ()

Giới hạn dữ liệu

  SELECT country_code
  , COUNT(athlete_id) as athletes
  FROM athletes
  WHERE year in (2014, 2010) -- Cột đã lập chỉ mục
  GROUP BY country_code

Giới hạn dữ liệu

  SELECT country_code
  , COUNT(athlete_id) as athletes
  FROM athletes
  WHERE year in (2014, 2010) -- Cột đã lập chỉ mục
  GROUP BY country_code

Không có Index	Có Index
Thời gian lập kế hoạch: 3.370 ms	Thời gian lập kế hoạch: 0.163 ms
Thời gian thực thi: 0.143 ms	Thời gian thực thi: 0.062 ms

Gộp nhóm - độ chi tiết khác nhau

SELECT r.country
  , COUNT(a.athlete_id) as athletes
FROM regions r -- cấp quốc gia
INNER JOIN athletes a -- cấp vận động viên
  ON r.country = a.country
GROUP BY r.country

Thời gian thực thi: 0.267 ms

Gộp nhóm - thay đổi độ chi tiết

WITH olympians AS ( -- cấp quốc gia
  SELECT country
  , COUNT(athlete_id) as athletes
  FROM athletes -- cấp vận động viên
  GROUP BY country
)
SELECT country, athletes
FROM regions r -- cấp quốc gia
INNER JOIN olympians o
  ON r.country = o.country

	Thời gian thực thi
Join trước	0.267 ms
Gộp nhóm trước	0.192 ms

Vamos praticar!

Cải thiện hiệu năng truy vấn trong PostgreSQL