Re: old synchronized scan patch

On Mon, 2006-12-04 at 18:45 -0500, Tom Lane wrote:
> "Luke Lonergan" <llonergan@greenplum.com> writes:
> > On 12/4/06 11:07 AM, "Jeff Davis" <pgsql@j-davis.com> wrote:
> >> Now that 8.3 is open, I was considering a revival of this old patch:
> >> http://archives.postgresql.org/pgsql-hackers/2005-02/msg00832.php
> 
> > Where I think sync scan could have a big benefit is for multi-user business
> > intelligence workloads where there are a few huge fact tables of interest to
> > a wide audience.  Example: 5 business analysts come to work at 9AM and start
> > ad-hoc queries expected to run in about 15 minutes each.
> 

> Anyway I think the major stumbling block is to be able to show that the
> patch has only negligible performance impact in cases where it's not
> able to provide a benefit.

My patch was specifically designed with this in mind, and unless I'm
missing something, should have negligible performance impact.

The initial implementation directly maps the Oid of the relation to a
location in an shared-memory table (not table as in relation, but table
as in area of memory). Then, it stores the page offset for that relation
each time it fetches a page for that relation in a sequential scan. When
a scan starts, it checks that number to see if it's in the valid range
for the relation's file, and if it is, that's where it starts the scan
in the file. If it's not in the valid range, it starts at offset 0.

Since I am not storing any pointers, and since the information is only
really a hint, I don't need to do any locking on that page.

In the case of a collision in the in-memory table, or if the offset in
the relation overflows the data type used to store it, it should be no
worse than current behavior.

Regards,Jeff Davis