From: Chris Travers <chris@metatrontech.com>
To: Lenard, Rohan (Rohan) <rlenard@avaya.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Need indexes on empty tables for good performance ?
Date: Fri, 26 Aug 2005 23:34:23 -0700
Message-ID: <4310096F.3020805@metatrontech.com> (raw)
In-Reply-To: <2773CAC687FD5F4689F526998C7E4E5F43F392@au3010avexu1.global.avaya.com>
References: <2773CAC687FD5F4689F526998C7E4E5F43F392@au3010avexu1.global.avaya.com>
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
Attachments:chris.vcf (text/x-vcard, 125B) download
begin:vcard
fn:Chris Travers
n:Travers;Chris
email;internet:chris@metatrontech.com
x-mozilla-html:FALSE
version:2.1
end:vcard
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: chris@metatrontech.com, rlenard@avaya.com
Subject: Re: Need indexes on empty tables for good performance ?
In-Reply-To: <4310096F.3020805@metatrontech.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