Обсуждение: (never executed) in the execution plan

Поиск
Список
Период
Сортировка

(never executed) in the execution plan

От
Thomas Kellerer
Дата:
I just encountered something like this in an execution plan:

->  Hash  (cost=19865.48..19865.48 rows=489 width=12) (never executed)
      Output: ly.total_count, ly.customer_id
      ->  Subquery Scan on ly  (cost=19864.50..19865.48 rows=489 width=12) (never executed)
            Output: ly.total_count, ly.customer_id
            ->  HashAggregate  (cost=19864.50..19864.99 rows=489 width=4) (never executed)
                  Output: orders_1.customer_id, count(*)
                  ->  Seq Scan on public.orders orders_1  (cost=0.00..19847.00 rows=3500 width=4) (never executed)
                        Output: orders_1.id, orders_1.customer_id, orders_1.order_date, orders_1.amount,
orders_1.sales_person_id
                        Filter: (date_part('year'::text, (orders_1.order_date)::timestamp without time zone) =
(date_part('year'::text,(('now'::cstring)::date)::timestamp without time zone) - 1::double precision)) 


The above is only a part of the execution plan and represents a derived table that is outer joined to the main table.

Postgres is correct to not execute this, because the condition in the sub-query will indeed not return any rows.

I can see why the Hash Aggregate and the Hash Join nodes can be marked as "(never executed").

But why does the Seq Scan node have the "(never executed)" as well?

I can't see how Postgres could tell that the condition won't return anything without actually doing the Seq Scan (there
isno index on the column order_date) 

Thomas

Re: (never executed) in the execution plan

От
Albe Laurenz
Дата:
Thomas Kellerer wrote:
> I just encountered something like this in an execution plan:
> 
> ->  Hash  (cost=19865.48..19865.48 rows=489 width=12) (never executed)
>       Output: ly.total_count, ly.customer_id
>       ->  Subquery Scan on ly  (cost=19864.50..19865.48 rows=489 width=12) (never executed)
>             Output: ly.total_count, ly.customer_id
>             ->  HashAggregate  (cost=19864.50..19864.99 rows=489 width=4) (never executed)
>                   Output: orders_1.customer_id, count(*)
>                   ->  Seq Scan on public.orders orders_1  (cost=0.00..19847.00 rows=3500 width=4) (never executed)
>                         Output: orders_1.id, orders_1.customer_id, orders_1.order_date, orders_1.amount,
orders_1.sales_person_id
>                         Filter: (date_part('year'::text, (orders_1.order_date)::timestamp without time zone) =
(date_part('year'::text,(('now'::cstring)::date)::timestamp without time zone) - 1::double precision))
 
> 
> 
> The above is only a part of the execution plan and represents a derived table that is outer joined to
> the main table.
> 
> Postgres is correct to not execute this, because the condition in the sub-query will indeed not return
> any rows.
> 
> I can see why the Hash Aggregate and the Hash Join nodes can be marked as "(never executed").
> 
> But why does the Seq Scan node have the "(never executed)" as well?
> 
> I can't see how Postgres could tell that the condition won't return anything without actually doing
> the Seq Scan (there is no index on the column order_date)

As far as I can tell, the solution is in the part of the plan that you didn't show.
PostgreSQL never executed any of these nodes because it didn't need them.

Maybe the "hash" node is on the right hand side of a nested loop join whose
left side returned no rows?

Yours,
Laurenz Albe