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

Re: Need indexes on empty tables for good performance ?

From: Chris Travers <chris(at)metatrontech(dot)com>
To: "Lenard, Rohan (Rohan)" <rlenard(at)avaya(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Need indexes on empty tables for good performance ?
Date: 2005-08-27 06:34:23
Message-ID: 4310096F.3020805@metatrontech.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

Attachment: chris.vcf
Description: text/x-vcard (127 bytes)

In response to

pgsql-performance by date

Next:From: Umit OztosunDate: 2005-08-27 09:31:13
Subject: Re: Weird performance drop after VACUUM
Previous:From: Chris TraversDate: 2005-08-27 06:30:08
Subject: Re: Performance for relative large DB

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