Hi,
I tried to run tpc-h queries, but some queries failed by the error on last week.
>Subject: Re: [HACKERS] ORDER/GROUP BY expression not found in targetlist
>Date: Thu, 09 Jun 2016 12:08:01 +0900
Today, I try it again by changing max_parallel_workers_per_gather parameter.
The result of Q1 is bellow. Is this bug in the Open items on wiki?
-------------
postgres=# set max_parallel_workers_per_gather = 0;
SET
postgres=# \i queries/1.explain.sql
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=43474.03..43474.03 rows=1 width=236) (actual
time=1039.583..1039.583 rows=1 loops=1)
-> Sort (cost=43474.03..43474.04 rows=6 width=236) (actual
time=1039.583..1039.583 rows=1 loops=1)
Sort Key: l_returnflag, l_linestatus
Sort Method: top-N heapsort Memory: 25kB
-> HashAggregate (cost=43473.83..43474.00 rows=6 width=236) (actual
time=1039.529..1039.534 rows=4 loops=1)
Group Key: l_returnflag, l_linestatus
-> Seq Scan on lineitem (cost=0.00..19668.15 rows=595142
width=25) (actual time=0.048..125.332 rows=595224 loops=1)
Filter: (l_shipdate <= '1998-09-22 00:00:00'::timestamp
without time zone)
Rows Removed by Filter: 5348
Planning time: 0.180 ms
Execution time: 1039.758 ms
(11 rows)
postgres=# set max_parallel_workers_per_gather = default;
SET
postgres=# \i queries/1.explain.sql
ERROR: ORDER/GROUP BY expression not found in targetlist
-------------
Regards,
Tatsuro Yamada
NTT OSS Center
On 2016/06/13 12:39, Thomas Munro wrote:
Hi,
What is going on here?
postgres=# create table logs as select generate_series(1,
1000000)::text as data;
SELECT 1000000
postgres=# insert into logs select * from logs;
INSERT 0 1000000
postgres=# insert into logs select * from logs;
INSERT 0 2000000
postgres=# insert into logs select * from logs;
INSERT 0 4000000
postgres=# insert into logs select * from logs;
INSERT 0 8000000
postgres=# insert into logs select * from logs;
INSERT 0 16000000
postgres=# analyze logs;
ANALYZE
postgres=# set max_parallel_workers_per_gather = 0;
SET
postgres=# explain select length(data) from logs group by length(data);
┌────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├────────────────────────────────────────────────────────────────────────────┤
│ Group (cost=5843157.07..6005642.13 rows=993989 width=4) │
│ Group Key: (length(data)) │
│ -> Sort (cost=5843157.07..5923157.11 rows=32000018 width=4) │
│ Sort Key: (length(data)) │
│ -> Seq Scan on logs (cost=0.00..541593.22 rows=32000018 width=4) │
└────────────────────────────────────────────────────────────────────────────┘
(5 rows)
postgres=# set max_parallel_workers_per_gather = 2;
SET
postgres=# explain select length(data) from logs group by length(data);
ERROR: ORDER/GROUP BY expression not found in targetlist
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
