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

Re: Need indexes on empty tables for good performance ?

From: "Lenard, Rohan (Rohan)" <rlenard(at)avaya(dot)com>
To: "Thomas F(dot) O'Connell" <tfo(at)sitening(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Need indexes on empty tables for good performance ?
Date: 2005-08-29 22:13:37
Message-ID: 2773CAC687FD5F4689F526998C7E4E5F489B7B@au3010avexu1.global.avaya.com (view raw or flat)
Thread:
Lists: pgsql-performance
Actually the indexes on the child table do seem to get used - I just
wanted to make sure there was no penalty not having indexes on the empty
parent tables.
 
You are right - the parent is the best way to get at the unknown
children ... 


  _____  

	From: Thomas F. O'Connell [mailto:tfo(at)sitening(dot)com] 
	Sent: Tuesday, August 30, 2005 6:15 AM
	To: Lenard, Rohan (Rohan)
	Cc: pgsql-performance(at)postgresql(dot)org
	Subject: Re: [PERFORM] Need indexes on empty tables for good
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(tm)

	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


Responses

pgsql-performance by date

Next:From: asif aliDate: 2005-08-29 22:59:12
Subject: Re: Weird performance drop after VACUUM
Previous:From: Jim C. NasbyDate: 2005-08-29 21:09:17
Subject: Re: Performance for relative large DB

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