From: Mischa Sandberg <mischa@ca.sophos.com>
To: PGSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Optimizer internals
Date: Thu, 15 Jun 2006 17:36:22 -0700
Message-ID: <4491FD06.50901@ca.sophos.com> (raw)
In-Reply-To: <1150396425.31200.66.camel@archimedes>
References: <c841561b0606151105v459d830era158b9eea5f59a7e@mail.gmail.com>
<1150396425.31200.66.camel@archimedes>
Mark Lewis wrote:
> On Thu, 2006-06-15 at 14:05 -0400, John Vincent wrote:
>> Now I've been told by our DBA that we should have been able to wholy
>> satisfy that query via the indexes.
> DB2 can satisfy the query using only indexes because DB2 doesn't do
> MVCC.
You can get pretty much the same effect with materialized views.
Create a table that LOOKS like the index (just those columns),
with a foreign key relationship to the original table (cascade delete),
and have the after-insert trigger on the main table write a row to the derived table.
Now (index and) query the skinny table.
Advantage of these tables: you can cluster them regularily,
because it doesn't hard-lock the main table.
--
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mischa@ca.sophos.com
Subject: Re: Optimizer internals
In-Reply-To: <4491FD06.50901@ca.sophos.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox