기준

포스트 그레스와 함께 가장 흥미로운 후보 테스트 9.4 과 9.5 의 중간 현실적인 테이블과 200K 행 에 purchases와 10,000 별개의customer_id ( 평균. 고객 당 20 행 ).

Postgres 9.5의 경우 효과적으로 86446 명의 개별 고객과의 2 차 테스트를 실시했습니다. 아래를 참조하십시오 ( 고객 당 평균 2.3 행 ).

설정

메인 테이블

CREATE TABLE purchases (
  id          serial
, customer_id int  -- REFERENCES customer
, total       int  -- could be amount of money in Cent
, some_column text -- to make the row bigger, more realistic
);

내가 사용 serial(아래 추가 PK 제약)과 정수를 customer_id그보다 일반적인 설정이기 때문에. 또한 some_column일반적으로 더 많은 열을 보충하기 위해 추가되었습니다 .

더미 데이터, PK, 인덱스-일반적인 테이블에는 죽은 튜플이 있습니다.

INSERT INTO purchases (customer_id, total, some_column)    -- insert 200k rows
SELECT (random() * 10000)::int             AS customer_id  -- 10k customers
     , (random() * random() * 100000)::int AS total
     , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM   generate_series(1,200000) g;

ALTER TABLE purchases ADD CONSTRAINT purchases_id_pkey PRIMARY KEY (id);

DELETE FROM purchases WHERE random() > 0.9; -- some dead rows

INSERT INTO purchases (customer_id, total, some_column)
SELECT (random() * 10000)::int             AS customer_id  -- 10k customers
     , (random() * random() * 100000)::int AS total
     , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM   generate_series(1,20000) g;  -- add 20k to make it ~ 200k

CREATE INDEX purchases_3c_idx ON purchases (customer_id, total DESC, id);

VACUUM ANALYZE purchases;

customer 테이블-우수한 쿼리

CREATE TABLE customer AS
SELECT customer_id, 'customer_' || customer_id AS customer
FROM   purchases
GROUP  BY 1
ORDER  BY 1;

ALTER TABLE customer ADD CONSTRAINT customer_customer_id_pkey PRIMARY KEY (customer_id);

VACUUM ANALYZE customer;

내에서 두 번째 테스트 9.5 나는 같은 설정을 사용할 수 있지만 함께하면 random() * 100000생성하는 customer_id당 몇 행을 얻을 customer_id.

테이블의 객체 크기 purchases

이 쿼리로 생성됩니다 .

               what                | bytes/ct | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
 core_relation_size                | 20496384 | 20 MB        |           102
 visibility_map                    |        0 | 0 bytes      |             0
 free_space_map                    |    24576 | 24 kB        |             0
 table_size_incl_toast             | 20529152 | 20 MB        |           102
 indexes_size                      | 10977280 | 10 MB        |            54
 total_size_incl_toast_and_indexes | 31506432 | 30 MB        |           157
 live_rows_in_text_representation  | 13729802 | 13 MB        |            68
 ------------------------------    |          |              |
 row_count                         |   200045 |              |
 live_tuples                       |   200045 |              |
 dead_tuples                       |    19955 |              |

쿼리

1. row_number()CTE에서 ( 다른 답변 참조 )

WITH cte AS (
   SELECT id, customer_id, total
        , row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
   FROM   purchases
   )
SELECT id, customer_id, total
FROM   cte
WHERE  rn = 1;

2. row_number()하위 쿼리에서 (내 최적화)

SELECT id, customer_id, total
FROM   (
   SELECT id, customer_id, total
        , row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
   FROM   purchases
   ) sub
WHERE  rn = 1;

3. DISTINCT ON( 다른 답변 참조 )

SELECT DISTINCT ON (customer_id)
       id, customer_id, total
FROM   purchases
ORDER  BY customer_id, total DESC, id;

4. LATERAL하위 쿼리가있는 rCTE ( 여기 참조 )

WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT id, customer_id, total
   FROM   purchases
   ORDER  BY customer_id, total DESC
   LIMIT  1
   )
   UNION ALL
   SELECT u.*
   FROM   cte c
   ,      LATERAL (
      SELECT id, customer_id, total
      FROM   purchases
      WHERE  customer_id > c.customer_id  -- lateral reference
      ORDER  BY customer_id, total DESC
      LIMIT  1
      ) u
   )
SELECT id, customer_id, total
FROM   cte
ORDER  BY customer_id;

5. customer테이블 LATERAL( 여기 참조 )

SELECT l.*
FROM   customer c
,      LATERAL (
   SELECT id, customer_id, total
   FROM   purchases
   WHERE  customer_id = c.customer_id  -- lateral reference
   ORDER  BY total DESC
   LIMIT  1
   ) l;

제 array_agg()와 ORDER BY( 다른 답을 참조 )

SELECT (array_agg(id ORDER BY total DESC))[1] AS id
     , customer_id
     , max(total) AS total
FROM   purchases
GROUP  BY customer_id;

결과

위의 쿼리에 대한 실행 시간 EXPLAIN ANALYZE(및 모든 옵션이 꺼져 있음 ), 최대 5 회 실행 .

모든 쿼리는 다른 단계 중에서 인덱스 전용 스캔 을 사용했습니다 purchases2_3c_idx. 그들 중 일부는 단지 더 작은 크기의 색인을 위해, 다른 일부는 더 효과적으로.

A. Postgres 9.4 (200k 행 및 ~ 20 개) customer_id

1. 273.274 ms
2. 194.572 ms
3. 111.067 ms
4.  92.922 ms
5.  37.679 ms  -- winner
6. 189.495 ms

B. Postgres 9.5와 동일

1. 288.006 ms
2. 223.032 ms
3. 107.074 ms
4.  78.032 ms
5.  33.944 ms  -- winner
6. 211.540 ms  

C. B와 동일하지만 ~ 2.3 행당 customer_id

1. 381.573 ms
2. 311.976 ms
3. 124.074 ms  -- winner
4. 710.631 ms
5. 311.976 ms
6. 421.679 ms

관련 벤치 마크

다음은 Postgres 11.5 (2019 년 9 월 현재) 에서 10M 개의 행과 60k의 고유 한 “고객” 을 사용한 “ogr”테스트에 의한 새로운 것 입니다. 결과는 여전히 우리가 지금까지 본 것과 일치합니다.

• 각 개별 식별자에 대한 최신 행에 액세스하는 올바른 방법은 무엇입니까?

2011 년의 원래 (오래된) 벤치 마크

PostgreSQL 9.1 을 사용하여 65579 행의 실제 테이블과 관련된 세 개의 열 각각에 대한 단일 열 btree 인덱스에서 세 가지 테스트를 실행 했으며 5 번의 실행 시간 을 가장 잘 수행했습니다 .
비교 @OMGPonies ‘ 첫 번째 쿼리를 ( A받는 사람) 위의 DISTINCT ON솔루션 ( B) :

1. 이 경우 전체 테이블을 선택하면 결과는 5958 행입니다.

   A: 567.218 ms
   B: 386.673 ms

2. WHERE customer BETWEEN x AND y1000 행의 결과 조건을 사용하십시오 .

   A: 249.136 ms
   B:  55.111 ms

3. 로 단일 고객을 선택하십시오 WHERE customer = x.

   A:   0.143 ms
   B:   0.072 ms

다른 답변에 설명 된 색인으로 동일한 테스트를 반복했습니다.

CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);

1A: 277.953 ms
1B: 193.547 ms

2A: 249.796 ms -- special index not used  
2B:  28.679 ms

3A:   0.120 ms
3B:   0.048 ms

답변

SELECT  customer,
        (array_agg(id ORDER BY total DESC))[1],
        max(total)
FROM purchases
GROUP BY customer

select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;

-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $1;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.first (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement
);

-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $2;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.last (
        sfunc    = public.last_agg,
        basetype = anyelement,
        stype    = anyelement
);

SELECT FIRST(id), customer, FIRST(total)
FROM  purchases
GROUP BY customer
ORDER BY FIRST(total) DESC;

apt-get install postgresql-server-dev-9.3 git build-essential -y
git clone git://github.com/wulczer/first_last_agg.git
cd first_last_app
make && sudo make install
psql -c 'create extension first_last_agg'

http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES

SELECT first(id order by id), customer, first(total order by id)
  FROM purchases
 GROUP BY customer
 ORDER BY first(total);