pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed indexes on empty tables for good performance ?
6+ messages / 5 participants
[nested] [flat]
* Need indexes on empty tables for good performance ?
@ 2005-08-23 03:41 Lenard, Rohan (Rohan) <rlenard@avaya.com>
0 siblings, 3 replies; 6+ messages in thread
From: Lenard, Rohan (Rohan) @ 2005-08-23 03:41 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Need indexes on empty tables for good performance ?
@ 2005-08-27 06:19 Bruno Wolff III <bruno@wolff.to>
parent: Lenard, Rohan (Rohan) <rlenard@avaya.com>
2 siblings, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2005-08-27 06:19 UTC (permalink / raw)
To: Lenard, Rohan (Rohan) <rlenard@avaya.com>; +Cc: pgsql-performance
On Tue, Aug 23, 2005 at 13:41:32 +1000,
"Lenard, Rohan (Rohan)" <rlenard@avaya.com> 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.
count will use indexes if appropiate. The counts themselves are NOT in the
indexes, so counts of significant fractions of a table (in particular
of the whole table) won't benefit from indexes.
You aren't going to get query speed ups by putting indexes on empty tables.
However, they may be required if you have unique or primary keys declared
in the table. You may want them to enforce some kinds of constraints.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Need indexes on empty tables for good performance ?
@ 2005-08-27 06:34 Chris Travers <chris@metatrontech.com>
parent: Lenard, Rohan (Rohan) <rlenard@avaya.com>
2 siblings, 0 replies; 6+ messages in thread
From: Chris Travers @ 2005-08-27 06:34 UTC (permalink / raw)
To: Lenard, Rohan (Rohan) <rlenard@avaya.com>; +Cc: 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
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
* Re: Need indexes on empty tables for good performance ?
@ 2005-08-29 20:15 Thomas F. O'Connell <tfo@sitening.com>
parent: Lenard, Rohan (Rohan) <rlenard@avaya.com>
2 siblings, 0 replies; 6+ messages in thread
From: Thomas F. O'Connell @ 2005-08-29 20:15 UTC (permalink / raw)
To: Lenard, Rohan (Rohan) <rlenard@avaya.com>; +Cc: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Need indexes on empty tables for good performance ?
@ 2005-08-29 22:13 Lenard, Rohan (Rohan) <rlenard@avaya.com>
0 siblings, 1 reply; 6+ messages in thread
From: Lenard, Rohan (Rohan) @ 2005-08-29 22:13 UTC (permalink / raw)
To: Thomas F. O'Connell <tfo@sitening.com>; +Cc: 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@sitening.com]
Sent: Tuesday, August 30, 2005 6:15 AM
To: Lenard, Rohan (Rohan)
Cc: pgsql-performance@postgresql.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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Need indexes on empty tables for good performance ?
@ 2005-08-30 17:43 Chris Travers <chris@travelamericas.com>
parent: Lenard, Rohan (Rohan) <rlenard@avaya.com>
0 siblings, 0 replies; 6+ messages in thread
From: Chris Travers @ 2005-08-30 17:43 UTC (permalink / raw)
To: Lenard, Rohan (Rohan) <rlenard@avaya.com>; +Cc: Thomas F. O'Connell <tfo@sitening.com>; pgsql-performance
Lenard, Rohan (Rohan) wrote:
> 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 ...
Indexes are created in the inheritance process, iirc. However, index
entries are not inherited, which means that index-based unique
constraints don't properly get inherited.
Best Wishes,
Chris Travers
Metatron Technology Consulting
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-08-30 17:43 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-23 03:41 Need indexes on empty tables for good performance ? Lenard, Rohan (Rohan) <rlenard@avaya.com>
2005-08-27 06:19 ` Bruno Wolff III <bruno@wolff.to>
2005-08-27 06:34 ` Chris Travers <chris@metatrontech.com>
2005-08-29 20:15 ` Thomas F. O'Connell <tfo@sitening.com>
2005-08-29 22:13 Re: Need indexes on empty tables for good performance ? Lenard, Rohan (Rohan) <rlenard@avaya.com>
2005-08-30 17:43 ` Chris Travers <chris@travelamericas.com>
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