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

Re: Need indexes on empty tables for good performance ?

From: "Thomas F(dot) O'Connell" <tfo(at)sitening(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-29 20:15:21
Message-ID: 2408BBD7-2C6D-4FAD-82ED-A8E9DC07D83A@sitening.com (view raw or flat)
Thread:
Lists: pgsql-performance
Rohan,

You should note that in Postgres, indexes are not inherited by child  
tables.

Also, it seems difficult to select from a child table whose name you  
don't know unless you access the parent. And if you are accessing the  
data via the parent, I'm reasonably certain that you will find that  
indexes aren't used (even if they exist on the children) as a result  
of the way the children are accessed.

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)

On Aug 22, 2005, at 10:41 PM, 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.
>
> This is not as silly as it sounds - with table inheritance you  
> might have table children with the data and a parent that is  
> empty.  It'd be nice to make sure postgresql knows to never really  
> look at the parent - especially is you don't know the names of all  
> the children ..
>
> Thoughts ?
>
> thx,
>   Rohan

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2005-08-29 20:19:05
Subject: Re: getting an index to work with partial indices ...
Previous:From: Marc G. FournierDate: 2005-08-29 19:56:15
Subject: getting an index to work with partial indices ...

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