Re: Materialiation is slower than non-materialized

On Mon, Mar 23, 2015 at 6:01 AM, Rajeev rastogi
<rajeev(dot)rastogi(at)huawei(dot)com> wrote:
> The cost of re-scan of SeqScan node is considered to be same scan of SeqScan
> node, which always assumes that the records is fetched from disk and hence
> disk access cost is added (As we don’t know really how much memory will be
> available to cache during execution).

That's a general problem not limited to materialize nodes. We might
choose to do a heap-sort rather than a quick-sort, but it may turn out
that the "tapes" we create end up in the OS buffer cache instead of on
physical storage; in fact, it's probably the common case. Scans are
costed using seq_page_cost and random_page_cost, but most of the time
the "random" page cost will not be the cost of a head seek, because
we'll find the data in the OS page cache. Some of the time it really
will be a head seek, but we have no idea whether that will happen in
any given case. The autovacuum cost delays have this problem too: a
"miss" in shared buffers may really be a hit in the OS page cache, but
we don't know.

> This e-mail and its attachments contain confidential information from
> HUAWEI, which
> is intended only for the person or entity whose address is listed above. Any
> use of the
> information contained herein in any way (including, but not limited to,
> total or partial
> disclosure, reproduction, or dissemination) by persons other than the
> intended
> recipient(s) is prohibited. If you receive this e-mail in error, please
> notify the sender by
> phone or email immediately and delete it!

This kind of disclaimer is inappropriate on a public mailing list.
Don't send confidential information to public mailing lists. You
probably don't have any legal right to control what happens to it
after that, regardless of what you put in your email.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company