in BeginCopyTo make materialized view using COPY TO instead of COPY (query).

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).
Date: 2024-12-19 06:28:21
Message-ID: CACJufxHVxnyRYy67hiPePNCPwVBMzhTQ6FaL9_Te5On9udG=yg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

hi.
generally COPY TO``COPY table`` is faster than ``COPY (select * from table)``.

in BeginCopyTo. we have
"""
else if (rel->rd_rel->relkind == RELKIND_MATVIEW)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("cannot copy from materialized view \"%s\"",
RelationGetRelationName(rel)),
errhint("Try the COPY (SELECT ...) TO variant.")));
"""
Since materialized views have physical storage,

we can make materialized views also using COPY table_name, instead of
COPY(query).

Some simple tests show around %3.7 or 4.3% speed up.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message John Naylor 2024-12-19 06:32:46 Re: Fix crash when non-creator being an iteration on shared radix tree
Previous Message Shlok Kyal 2024-12-19 06:27:12 Re: Logical replication timeout