Re: POC: GROUP BY optimization

Поиск
Список
Период
Сортировка
От jian he
Тема Re: POC: GROUP BY optimization
Дата
Msg-id CACJufxGt99nZ+nir+aB6pFQ=K8oNiHAQ3OELqSbGMqNxok8nxA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: POC: GROUP BY optimization  (Alexander Korotkov <aekorotkov@gmail.com>)
Ответы Re: POC: GROUP BY optimization
Re: POC: GROUP BY optimization
Список pgsql-hackers
hi.
I found an interesting case.

CREATE TABLE t1 AS
  SELECT (i % 10)::numeric AS x,(i % 10)::int8 AS y,'abc' || i % 10 AS
z, i::int4 AS w
  FROM generate_series(1, 100) AS i;
CREATE INDEX t1_x_y_idx ON t1 (x, y);
ANALYZE t1;
SET enable_hashagg = off;
SET enable_seqscan = off;

EXPLAIN (COSTS OFF) SELECT count(*) FROM t1 GROUP BY x,z,y,w;
EXPLAIN (COSTS OFF) SELECT count(*) FROM t1 GROUP BY x,w,y,z;
EXPLAIN (COSTS OFF) SELECT count(*) FROM t1 GROUP BY x,z,w,y;
EXPLAIN (COSTS OFF) SELECT count(*) FROM t1 GROUP BY x,w,z,y;
the above part will use:
  ->  Incremental Sort
         Sort Key: x, $, $, $
         Presorted Key: x
         ->  Index Scan using t1_x_y_idx on t1

EXPLAIN (COSTS OFF) SELECT count(*) FROM t1 GROUP BY z,y,w,x;
EXPLAIN (COSTS OFF) SELECT count(*) FROM t1 GROUP BY w,y,z,x;
EXPLAIN (COSTS OFF) SELECT count(*) FROM t1 GROUP BY y,z,x,w;
EXPLAIN (COSTS OFF) SELECT count(*) FROM t1 GROUP BY y,w,x,z;
EXPLAIN (COSTS OFF) SELECT count(*) FROM t1 GROUP BY y,x,z,w;
EXPLAIN (COSTS OFF) SELECT count(*) FROM t1 GROUP BY y,x,w,z;

these will use:
  ->  Incremental Sort
         Sort Key: x, y, $, $
         Presorted Key: x, y
         ->  Index Scan using t1_x_y_idx on t1

I guess this is fine, but not optimal?



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

Предыдущее
От: David Rowley
Дата:
Сообщение: Re: Add memory context type to pg_backend_memory_contexts view
Следующее
От: Amit Kapila
Дата:
Сообщение: Re: Race condition in FetchTableStates() breaks synchronization of subscription tables