explain plan difference - Mailing list pgsql-general
| From | Steve Baldwin |
|---|---|
| Subject | explain plan difference |
| Date | |
| Msg-id | CAKE1AiYdwjx_Wq07vE=uwHeDHa8cL35iD_GyqHrKfJ3WihHBkg@mail.gmail.com Whole thread Raw |
| Responses |
Re: explain plan difference
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-general |
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
pgsql-general by date: