From: | Mischa Sandberg <mischa(at)ca(dot)sophos(dot)com> |
---|---|
To: | PGSQL Performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Optimizer internals |
Date: | 2006-06-16 00:36:22 |
Message-ID: | 4491FD06.50901@ca.sophos.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2006-06-16 01:03:56 | Re: Performance of pg_dump on PGSQL 8.0 |
Previous Message | Alex Turner | 2006-06-15 23:58:00 | Re: SAN performance mystery |