pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Indexes (Disk space)
6+ messages / 3 participants
[nested] [flat]

* Indexes (Disk space)
@ 2005-08-22 17:37 jose fuenmayor <jafn82@gmail.com>
  2005-08-23 18:01 ` Fwd: Indexes (Disk space) jose fuenmayor <jafn82@gmail.com>
  0 siblings, 1 reply; 6+ messages in thread

From: jose fuenmayor @ 2005-08-22 17:37 UTC (permalink / raw)
  To: pgsql-admin

Hi all, 
I have the following question.
Is there anyway for me to know how much space on disk will ocupy an
index, created in a determined row or rows of a table?, anything like
a rule, formula, calculation? that allow me to know in advance how
much space will the index use before actually created it.

I aprecciate all the help you can give me

Thanks in advance.



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Fwd: Indexes (Disk space)
  2005-08-22 17:37 Indexes (Disk space) jose fuenmayor <jafn82@gmail.com>
@ 2005-08-23 18:01 ` jose fuenmayor <jafn82@gmail.com>
  2005-08-23 19:39   ` Re: Fwd: Indexes (Disk space) Aldor <an@mediaroot.de>
  0 siblings, 1 reply; 6+ messages in thread

From: jose fuenmayor @ 2005-08-23 18:01 UTC (permalink / raw)
  To: pgsql-admin

---------- Forwarded message ----------
From: jose fuenmayor <jafn82@gmail.com>
Date: Aug 22, 2005 1:37 PM
Subject: Indexes (Disk space)
To: pgsql-admin@postgresql.org


Hi all,
I have the following question.
Is there anyway for me to know how much space on disk will ocupy an
index, created in a determined row or rows of a table?, anything like
a rule, formula, calculation? that allow me to know in advance how
much space will the index use before actually created it.

I aprecciate all the help you can give me

Thanks in advance.



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Fwd: Indexes (Disk space)
  2005-08-22 17:37 Indexes (Disk space) jose fuenmayor <jafn82@gmail.com>
  2005-08-23 18:01 ` Fwd: Indexes (Disk space) jose fuenmayor <jafn82@gmail.com>
@ 2005-08-23 19:39   ` Aldor <an@mediaroot.de>
  2005-08-24 12:43     ` Re: Fwd: Indexes (Disk space) jose fuenmayor <jafn82@gmail.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Aldor @ 2005-08-23 19:39 UTC (permalink / raw)
  To: jose fuenmayor <jafn82@gmail.com>; +Cc: pgsql-admin

select * from pg_class;

or

select relname, relpages from pg_class where relname = '[index-name]';

the pages give you the information about the space the index uses, a 
page has 8kb.

[...]Every table and index is stored as an array of pages of a fixed 
size (usually 8Kb, although a different page size can be selected when 
compiling the server). In a table, all the pages are logically 
equivalent, so a particular item (row) can be stored in any page. In 
indexes, the first page is generally reserved as a metapage holding 
control information, and there may be different types of pages within 
the index, depending on the index access method. [...]

Source-URL: 
http://www.postgresql.org/docs/8.0/interactive/storage-page-layout.html

jose fuenmayor wrote:
> ---------- Forwarded message ----------
> From: jose fuenmayor <jafn82@gmail.com>
> Date: Aug 22, 2005 1:37 PM
> Subject: Indexes (Disk space)
> To: pgsql-admin@postgresql.org
> 
> 
> Hi all,
> I have the following question.
> Is there anyway for me to know how much space on disk will ocupy an
> index, created in a determined row or rows of a table?, anything like
> a rule, formula, calculation? that allow me to know in advance how
> much space will the index use before actually created it.
> 
> I aprecciate all the help you can give me
> 
> Thanks in advance.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
> 



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Fwd: Indexes (Disk space)
  2005-08-22 17:37 Indexes (Disk space) jose fuenmayor <jafn82@gmail.com>
  2005-08-23 18:01 ` Fwd: Indexes (Disk space) jose fuenmayor <jafn82@gmail.com>
  2005-08-23 19:39   ` Re: Fwd: Indexes (Disk space) Aldor <an@mediaroot.de>
@ 2005-08-24 12:43     ` jose fuenmayor <jafn82@gmail.com>
  2005-08-24 13:51       ` Re: Fwd: Indexes (Disk space) Aldor <an@mediaroot.de>
  0 siblings, 1 reply; 6+ messages in thread

From: jose fuenmayor @ 2005-08-24 12:43 UTC (permalink / raw)
  To: Aldor <an@mediaroot.de>; pgsql-admin

Ok I understand but I need to know the size of the index before i create it
I need to know  how much space it will ocupy (before actually create
the index, having the information of the table on wich I will build
the index), is this possible?
thanks in advance to everyone that can help me.

On 8/23/05, Aldor <an@mediaroot.de> wrote:
> select * from pg_class;
> 
> or
> 
> select relname, relpages from pg_class where relname = '[index-name]';
> 
> the pages give you the information about the space the index uses, a
> page has 8kb.
> 
> [...]Every table and index is stored as an array of pages of a fixed
> size (usually 8Kb, although a different page size can be selected when
> compiling the server). In a table, all the pages are logically
> equivalent, so a particular item (row) can be stored in any page. In
> indexes, the first page is generally reserved as a metapage holding
> control information, and there may be different types of pages within
> the index, depending on the index access method. [...]
> 
> Source-URL:
> http://www.postgresql.org/docs/8.0/interactive/storage-page-layout.html
> 
> jose fuenmayor wrote:
> > ---------- Forwarded message ----------
> > From: jose fuenmayor <jafn82@gmail.com>
> > Date: Aug 22, 2005 1:37 PM
> > Subject: Indexes (Disk space)
> > To: pgsql-admin@postgresql.org
> >
> >
> > Hi all,
> > I have the following question.
> > Is there anyway for me to know how much space on disk will ocupy an
> > index, created in a determined row or rows of a table?, anything like
> > a rule, formula, calculation? that allow me to know in advance how
> > much space will the index use before actually created it.
> >
> > I aprecciate all the help you can give me
> >
> > Thanks in advance.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> >        subscribe-nomail command to majordomo@postgresql.org so that your
> >        message can get through to the mailing list cleanly
> >
>



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Fwd: Indexes (Disk space)
  2005-08-22 17:37 Indexes (Disk space) jose fuenmayor <jafn82@gmail.com>
  2005-08-23 18:01 ` Fwd: Indexes (Disk space) jose fuenmayor <jafn82@gmail.com>
  2005-08-23 19:39   ` Re: Fwd: Indexes (Disk space) Aldor <an@mediaroot.de>
  2005-08-24 12:43     ` Re: Fwd: Indexes (Disk space) jose fuenmayor <jafn82@gmail.com>
@ 2005-08-24 13:51       ` Aldor <an@mediaroot.de>
  2005-08-24 18:17         ` Re: Fwd: Indexes (Disk space) Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Aldor @ 2005-08-24 13:51 UTC (permalink / raw)
  To: jose fuenmayor <jafn82@gmail.com>; +Cc: pgsql-admin

I don't think this is possible...

you could try a explain on the create
index command but... this is like playinng lotto, didn't try it and 
don't have the time to try it;-)

i think getting the size is only possible after creating the index, 
because when the index is created it will gather the information about 
the data indexed.

if this answer is not enough: why do you need to know the size of the 
index BEFORE creating it?

jose fuenmayor wrote:
> Ok I understand but I need to know the size of the index before i create it
> I need to know  how much space it will ocupy (before actually create
> the index, having the information of the table on wich I will build
> the index), is this possible?
> thanks in advance to everyone that can help me.
> 
> On 8/23/05, Aldor <an@mediaroot.de> wrote:
> 
>>select * from pg_class;
>>
>>or
>>
>>select relname, relpages from pg_class where relname = '[index-name]';
>>
>>the pages give you the information about the space the index uses, a
>>page has 8kb.
>>
>>[...]Every table and index is stored as an array of pages of a fixed
>>size (usually 8Kb, although a different page size can be selected when
>>compiling the server). In a table, all the pages are logically
>>equivalent, so a particular item (row) can be stored in any page. In
>>indexes, the first page is generally reserved as a metapage holding
>>control information, and there may be different types of pages within
>>the index, depending on the index access method. [...]
>>
>>Source-URL:
>>http://www.postgresql.org/docs/8.0/interactive/storage-page-layout.html
>>
>>jose fuenmayor wrote:
>>
>>>---------- Forwarded message ----------
>>>From: jose fuenmayor <jafn82@gmail.com>
>>>Date: Aug 22, 2005 1:37 PM
>>>Subject: Indexes (Disk space)
>>>To: pgsql-admin@postgresql.org
>>>
>>>
>>>Hi all,
>>>I have the following question.
>>>Is there anyway for me to know how much space on disk will ocupy an
>>>index, created in a determined row or rows of a table?, anything like
>>>a rule, formula, calculation? that allow me to know in advance how
>>>much space will the index use before actually created it.
>>>
>>>I aprecciate all the help you can give me
>>>
>>>Thanks in advance.
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 1: if posting/reading through Usenet, please send an appropriate
>>>       subscribe-nomail command to majordomo@postgresql.org so that your
>>>       message can get through to the mailing list cleanly
>>>
>>
> 



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Fwd: Indexes (Disk space)
  2005-08-22 17:37 Indexes (Disk space) jose fuenmayor <jafn82@gmail.com>
  2005-08-23 18:01 ` Fwd: Indexes (Disk space) jose fuenmayor <jafn82@gmail.com>
  2005-08-23 19:39   ` Re: Fwd: Indexes (Disk space) Aldor <an@mediaroot.de>
  2005-08-24 12:43     ` Re: Fwd: Indexes (Disk space) jose fuenmayor <jafn82@gmail.com>
  2005-08-24 13:51       ` Re: Fwd: Indexes (Disk space) Aldor <an@mediaroot.de>
@ 2005-08-24 18:17         ` Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Jim C. Nasby @ 2005-08-24 18:17 UTC (permalink / raw)
  To: Aldor <an@mediaroot.de>; +Cc: jose fuenmayor <jafn82@gmail.com>; pgsql-admin

I think the best you could do would be to estimate it based on index key
size and knowing the overhead for the index structure. Note that it
probably wouldn't be too hard to codify this; it's just that no one has
done it.

http://www.postgresql.org/docs/8.0/interactive/storage.html would be a
good start at figuring out how much space the index will take.

On Wed, Aug 24, 2005 at 02:51:30PM +0100, Aldor wrote:
> I don't think this is possible...
> 
> you could try a explain on the create
> index command but... this is like playinng lotto, didn't try it and 
> don't have the time to try it;-)
> 
> i think getting the size is only possible after creating the index, 
> because when the index is created it will gather the information about 
> the data indexed.
> 
> if this answer is not enough: why do you need to know the size of the 
> index BEFORE creating it?
> 
> jose fuenmayor wrote:
> >Ok I understand but I need to know the size of the index before i create it
> >I need to know  how much space it will ocupy (before actually create
> >the index, having the information of the table on wich I will build
> >the index), is this possible?
> >thanks in advance to everyone that can help me.
> >
> >On 8/23/05, Aldor <an@mediaroot.de> wrote:
> >
> >>select * from pg_class;
> >>
> >>or
> >>
> >>select relname, relpages from pg_class where relname = '[index-name]';
> >>
> >>the pages give you the information about the space the index uses, a
> >>page has 8kb.
> >>
> >>[...]Every table and index is stored as an array of pages of a fixed
> >>size (usually 8Kb, although a different page size can be selected when
> >>compiling the server). In a table, all the pages are logically
> >>equivalent, so a particular item (row) can be stored in any page. In
> >>indexes, the first page is generally reserved as a metapage holding
> >>control information, and there may be different types of pages within
> >>the index, depending on the index access method. [...]
> >>
> >>Source-URL:
> >>http://www.postgresql.org/docs/8.0/interactive/storage-page-layout.html
> >>
> >>jose fuenmayor wrote:
> >>
> >>>---------- Forwarded message ----------
> >>>From: jose fuenmayor <jafn82@gmail.com>
> >>>Date: Aug 22, 2005 1:37 PM
> >>>Subject: Indexes (Disk space)
> >>>To: pgsql-admin@postgresql.org
> >>>
> >>>
> >>>Hi all,
> >>>I have the following question.
> >>>Is there anyway for me to know how much space on disk will ocupy an
> >>>index, created in a determined row or rows of a table?, anything like
> >>>a rule, formula, calculation? that allow me to know in advance how
> >>>much space will the index use before actually created it.
> >>>
> >>>I aprecciate all the help you can give me
> >>>
> >>>Thanks in advance.
> >>>
> >>>---------------------------(end of broadcast)---------------------------
> >>>TIP 1: if posting/reading through Usenet, please send an appropriate
> >>>      subscribe-nomail command to majordomo@postgresql.org so that your
> >>>      message can get through to the mailing list cleanly
> >>>
> >>
> >
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software        http://pervasive.com        512-569-9461



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2005-08-24 18:17 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-22 17:37 Indexes (Disk space) jose fuenmayor <jafn82@gmail.com>
2005-08-23 18:01 ` jose fuenmayor <jafn82@gmail.com>
2005-08-23 19:39   ` Aldor <an@mediaroot.de>
2005-08-24 12:43     ` jose fuenmayor <jafn82@gmail.com>
2005-08-24 13:51       ` Aldor <an@mediaroot.de>
2005-08-24 18:17         ` Jim C. Nasby <jnasby@pervasive.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