Re: [PERFORM] Need indexes on empty tables for good 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Need indexes on empty tables for good 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:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 6:15 AMTo: Lenard, Rohan (Rohan)Cc: pgsql-performance@postgresql.orgSubject: 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™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150615-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
Re: [PERFORM] Need indexes on empty tables for good 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 begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 6: explain analyze is your friend
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'ConnellCo-Founder, Information ArchitectSitening, LLCStrategic Open Source: Open Your i™http://www.sitening.com/110 30th Avenue North, Suite 6Nashville, TN 37203-6320615-469-5150615-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
Re: [PERFORM] Need indexes on empty tables for good performance ?
On Tue, Aug 23, 2005 at 13:41:32 +1000, "Lenard, Rohan (Rohan)" <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
