Thread: explain plan difference
Can someone please help me understand this:
b2bc_dev=# vacuum full analyze invoice;
VACUUM
Time: 39.671 ms
b2bc_dev=# create table invoice_copy as select * from invoice;
SELECT 23
Time: 11.557 ms
b2bc_dev=# alter table invoice_copy add primary key (id);
ALTER TABLE
Time: 9.257 ms
VACUUM
Time: 39.671 ms
b2bc_dev=# create table invoice_copy as select * from invoice;
SELECT 23
Time: 11.557 ms
b2bc_dev=# alter table invoice_copy add primary key (id);
ALTER TABLE
Time: 9.257 ms
b2bc_dev=# vacuum full analyze invoice_copy;
VACUUM
Time: 24.369 ms
b2bc_dev=# explain analyze verbose select max(id::text) from invoice;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Aggregate (cost=4.40..4.41 rows=1 width=32) (actual time=0.852..0.878 rows=1 loops=1)
Output: max((id)::text)
-> Seq Scan on public.invoice (cost=0.00..4.23 rows=23 width=16) (actual time=0.016..0.440 rows=23 loops=1)
Output: id
Planning time: 0.359 ms
Execution time: 1.063 ms
(6 rows)
Time: 4.266 ms
b2bc_dev=# explain analyze verbose select max(id::text) from invoice_copy;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4.40..4.41 rows=1 width=32) (actual time=0.981..1.019 rows=1 loops=1)
Output: max((id)::text)
-> Seq Scan on public.invoice_copy (cost=0.00..4.23 rows=23 width=16) (actual time=0.016..0.464 rows=23 loops=1)
Output: id, client_reference_id, buyer_id, seller_id, invoice_date, invoice_due_date, invoice_total, po_number, details, status, statement_id, invoice_reference_number, created, invoice_net, ar_open_total, bill_date, return_total, api_client_id, client_id, program_id, billing_payment_term_config_id, preauthorization_id, tap_synced, updated, shipping_amount, tax_amount, foreign_exchange_fee, foreign_exchange_fee_rate, return_foreign_exchange_fee, original_shipping_amount, original_tax_amount, discount_amount, original_discount_amount, shipping_tax_amount, shipping_discount_amount, original_shipping_tax_amount, original_shipping_discount_amount, captured_amount_seller, captured_amount_buyer, adjusted_amount, ar_closed_on
Planning time: 0.441 ms
Execution time: 1.254 ms
(6 rows)
b2bc_dev=# select version();
version
---------------------------------------------------------------------------------------
PostgreSQL 10.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 6.4.0) 6.4.0, 64-bit
(1 row)
Time: 4.916 ms
VACUUM
Time: 24.369 ms
b2bc_dev=# explain analyze verbose select max(id::text) from invoice;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Aggregate (cost=4.40..4.41 rows=1 width=32) (actual time=0.852..0.878 rows=1 loops=1)
Output: max((id)::text)
-> Seq Scan on public.invoice (cost=0.00..4.23 rows=23 width=16) (actual time=0.016..0.440 rows=23 loops=1)
Output: id
Planning time: 0.359 ms
Execution time: 1.063 ms
(6 rows)
Time: 4.266 ms
b2bc_dev=# explain analyze verbose select max(id::text) from invoice_copy;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4.40..4.41 rows=1 width=32) (actual time=0.981..1.019 rows=1 loops=1)
Output: max((id)::text)
-> Seq Scan on public.invoice_copy (cost=0.00..4.23 rows=23 width=16) (actual time=0.016..0.464 rows=23 loops=1)
Output: id, client_reference_id, buyer_id, seller_id, invoice_date, invoice_due_date, invoice_total, po_number, details, status, statement_id, invoice_reference_number, created, invoice_net, ar_open_total, bill_date, return_total, api_client_id, client_id, program_id, billing_payment_term_config_id, preauthorization_id, tap_synced, updated, shipping_amount, tax_amount, foreign_exchange_fee, foreign_exchange_fee_rate, return_foreign_exchange_fee, original_shipping_amount, original_tax_amount, discount_amount, original_discount_amount, shipping_tax_amount, shipping_discount_amount, original_shipping_tax_amount, original_shipping_discount_amount, captured_amount_seller, captured_amount_buyer, adjusted_amount, ar_closed_on
Planning time: 0.441 ms
Execution time: 1.254 ms
(6 rows)
b2bc_dev=# select version();
version
---------------------------------------------------------------------------------------
PostgreSQL 10.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 6.4.0) 6.4.0, 64-bit
(1 row)
Time: 4.916 ms
I guess the difference doesn't have a huge bearing (as far as I can tell) on the result, but it just seems odd that the inner-most 'Output' step outputs the entire row in the case of the copy and only the required field in the case of the original table. What triggers that behaviour?
Thanks,
Steve
Steve Baldwin <steve.baldwin@gmail.com> writes:
> I guess the difference doesn't have a huge bearing (as far as I can tell)
> on the result, but it just seems odd that the inner-most 'Output' step
> outputs the entire row in the case of the copy and only the required field
> in the case of the original table. What triggers that behaviour?
The plan with the full output row is actually slightly cheaper, or at
least so the planner thinks, because it saves a projection step.
I imagine the reason you're not getting that with the original table
is that there are some dropped column(s) in the original table, forcing
the projection to be done to get rid of them.
regards, tom lane
Thanks very much for the explanation Tom !! You are correct - there are dropped columns in the original.
Cheers,
Steve
On Mon, Nov 4, 2019 at 3:01 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Steve Baldwin <steve.baldwin@gmail.com> writes:
> I guess the difference doesn't have a huge bearing (as far as I can tell)
> on the result, but it just seems odd that the inner-most 'Output' step
> outputs the entire row in the case of the copy and only the required field
> in the case of the original table. What triggers that behaviour?
The plan with the full output row is actually slightly cheaper, or at
least so the planner thinks, because it saves a projection step.
I imagine the reason you're not getting that with the original table
is that there are some dropped column(s) in the original table, forcing
the projection to be done to get rid of them.
regards, tom lane
> On Nov 3, 2019, at 11:03 PM, Steve Baldwin <steve.baldwin@gmail.com> wrote: > > Thanks very much for the explanation Tom !! You are correct - there are dropped columns in the original. Just to confirm, we are talking about tables from where some cols were deleted in the past, but VACUUM FULL not run on that table, right ?
Ravi Krishna <srkrishna@yahoo.com> writes:
>> On Nov 3, 2019, at 11:03 PM, Steve Baldwin <steve.baldwin@gmail.com> wrote:
>> Thanks very much for the explanation Tom !! You are correct - there are dropped columns in the original.
> Just to confirm, we are talking about tables from where some cols were deleted in the past, but
> VACUUM FULL not run on that table, right ?
VACUUM would not change the state of the dropped columns.
regards, tom lane
>> Just to confirm, we are talking about tables from where some cols were deleted in the past, but >> VACUUM FULL not run on that table, right ? > > VACUUM would not change the state of the dropped columns. > When does it change?
Ravi Krishna <srkrishna@yahoo.com> writes:
>> VACUUM would not change the state of the dropped columns.
> When does it change?
Never, unless you drop and recreate the table. Removing a dropped
column would change the attnums of following columns, which we
can't support because the tableoid+attnum is the only persistent
identifier of a column.
(From memory, operations like VACUUM FULL and CLUSTER will rewrite
dropped columns with NULLs to reduce their storage impact. But they
don't go away.)
regards, tom lane
> > Never, unless you drop and recreate the table. Removing a dropped > column would change the attnums of following columns, which we > can't support because the tableoid+attnum is the only persistent > identifier of a column. > > (From memory, operations like VACUUM FULL and CLUSTER will rewrite > dropped columns with NULLs to reduce their storage impact. But they > don't go away.) > > Thank you. I remember reading it here that VACUUM FULL does what you describe above. So even TRUNCATE does not help here?