Re: Avoid detoast overhead when possible - Mailing list pgsql-hackers

From Nikita Malakhov
Subject Re: Avoid detoast overhead when possible
Date
Msg-id CAN-LCVP9ojEhfh12TOsynMXFw+nNMM693tygdQ6RLREm5T21EA@mail.gmail.com
Whole thread Raw
In response to Re: Avoid detoast overhead when possible  (zhihuifan1213@163.com)
Responses Re: Avoid detoast overhead when possible
List pgsql-hackers
Hi,

With your setup (table created with setup.sql):
postgres@postgres=# explain analyze select big->'1', big->'2', big->'3', big->'5', big->'10' from b;
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Seq Scan on b  (cost=0.00..29.52 rows=1001 width=160) (actual time=0.656..359.964 rows=1001 loops=1)
 Planning Time: 0.042 ms
 Execution Time: 360.177 ms
(3 rows)

Time: 361.054 ms
postgres@postgres=# explain analyze select big->'1' from b;
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Seq Scan on b  (cost=0.00..19.51 rows=1001 width=32) (actual time=0.170..63.996 rows=1001 loops=1)
 Planning Time: 0.042 ms
 Execution Time: 64.063 ms
(3 rows)

Time: 64.626 ms

Without patch, the same table and queries:
postgres@postgres=# explain analyze select big->'1', big->'2', big->'3', big->'5', big->'10' from b;
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Seq Scan on b  (cost=0.00..29.52 rows=1001 width=160) (actual time=0.665..326.399 rows=1001 loops=1)
 Planning Time: 0.035 ms
 Execution Time: 326.508 ms
(3 rows)

Time: 327.132 ms
postgres@postgres=# explain analyze select big->'1' from b;
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Seq Scan on b  (cost=0.00..19.51 rows=1001 width=32) (actual time=0.159..62.807 rows=1001 loops=1)
 Planning Time: 0.033 ms
 Execution Time: 62.879 ms
(3 rows)

Time: 63.504 ms

--
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company

pgsql-hackers by date:

Previous
From: Alena Rybakina
Date:
Subject: Re: POC PATCH: copy from ... exceptions to: (was Re: VLDB Features)
Next
From: shveta malik
Date:
Subject: Re: Synchronizing slots from primary to standby