Message-ID: <4310096F.3020805@metatrontech.com>
Date: Fri, 26 Aug 2005 23:34:23 -0700
From: Chris Travers <chris@metatrontech.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: "Lenard, Rohan (Rohan)" <rlenard@avaya.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Need indexes on empty tables for good performance ?
References: 
 <2773CAC687FD5F4689F526998C7E4E5F43F392@au3010avexu1.global.avaya.com>
In-Reply-To: 
 <2773CAC687FD5F4689F526998C7E4E5F43F392@au3010avexu1.global.avaya.com>
Content-Type: multipart/mixed; boundary="------------060901010708010604050506"

This is a multi-part message in MIME format.
--------------060901010708010604050506
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Lenard, Rohan (Rohan) wrote:

> I've read that indexes aren't used for COUNT(*) and I've noticed 
> (7.3.x) with EXPLAIN that indexes never seem to be used on empty 
> tables - is there any reason to have indexes on empty tables, or will 
> postgresql never use them.

You could add a row, vacuum analyze, delete the row, etc....  Then you 
are fine until you vacuum analyze again ;-)

This is a feature designed to prevent really bad plans when you are 
loading tables with data.  However, you are right.  It can create bad 
plans sometimes.

Any chance one can eventually come up with a way to tell the planner 
that an empty table is expected not to grow?  Otherwise, I can see 
nightmares in a data warehouse environment where you have an empty 
parent table...

Best Wishes,
Chris Travers
Metatron Technology Consulting

--------------060901010708010604050506
Content-Type: text/x-vcard; charset=utf-8;
 name="chris.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="chris.vcf"

begin:vcard
fn:Chris Travers
n:Travers;Chris
email;internet:chris@metatrontech.com
x-mozilla-html:FALSE
version:2.1
end:vcard


--------------060901010708010604050506--