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 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 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
Re: explain plan difference
Steve Baldwin 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
Re: explain plan difference
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 wrote: > Steve Baldwin 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 >
Re: explain plan difference
> On Nov 3, 2019, at 11:03 PM, Steve Baldwin 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 ?
Re: explain plan difference
Ravi Krishna writes: >> On Nov 3, 2019, at 11:03 PM, Steve Baldwin 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
Re: explain plan difference
>> 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?
Re: explain plan difference
Ravi Krishna 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
Re: explain plan difference
> > 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?
