Skip site navigation (1) Skip section navigation (2)

Re: planner/optimizer question

From: Kris Jurka <books(at)ejurka(dot)com>
To: Gary Doades <gpd(at)gpdnet(dot)co(dot)uk>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: planner/optimizer question
Date: 2004-04-30 19:48:32
Message-ID: Pine.BSO.4.56.0404301439590.29884@leary.csoft.net (view raw or flat)
Thread:
Lists: pgsql-performance

On Fri, 30 Apr 2004, Gary Doades wrote:

> Yes, you're right and I have done this just to prove to myself that it
> is the index scan that is the bottleneck. I have some complex SQL that
> executes very quickly with Postgres, similar to MSSQL, but the index
> scans in most of those only touch a few rows for a few loops. It seems
> to be a problem when the index scan is scanning very many rows and for
> each of these it has to go to the table just to find out if the index it
> just looked at is still valid.
> 

Another way to speed this up is the TODO item: "Use bitmaps to fetch 
heap pages in sequential order"  For an indexscan that fetches a number 
of rows those rows may be anywhere in the base table so as each index 
entry is found it fetches the corresponding table row from the heap.  This 
is not ideal because you can be jumping around in the heap and end up 
fetching the same page multiple times because table rows are in the same 
page, but were found in different places in the index.

Kris Jurka

In response to

pgsql-performance by date

Next:From: Manfred KoizarDate: 2004-04-30 21:36:58
Subject: Re: planner/optimizer question
Previous:From: Gary DoadesDate: 2004-04-30 18:59:38
Subject: Re: planner/optimizer question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group