pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Index occupancy 2+ messages / 2 participants [nested] [flat]
* Index occupancy @ 2006-02-02 14:03 tschak <jochen.schlosser@gmail.com> 0 siblings, 1 reply; 2+ messages in thread From: tschak @ 2006-02-02 14:03 UTC (permalink / raw) To: pgsql-performance Hi everyone, I have a question concerning the size of an index... What I acually did was bulid a btree index on an smallint attribute within a table with 10^8 rows. The table itself is app. 10GB large and what I would like to have the smallest possible indeces. Unfortunately the current size is about 2GB per indexed column (8 columns are indexed in total) which is too large if the planner is supposed to choose a bitmap scan between all of the indices. So what I would like to know is the following: Is there an easy way to tell postgres to occupy the index pages up to 100 %? I am working in a decision support system so inserts/deletes etc. do normally not happen at all? Thanks, Tschak ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Index occupancy @ 2006-02-05 00:49 Tom Lane <tgl@sss.pgh.pa.us> parent: tschak <jochen.schlosser@gmail.com> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2006-02-05 00:49 UTC (permalink / raw) To: tschak <jochen.schlosser@gmail.com>; +Cc: pgsql-performance "tschak" <jochen.schlosser@gmail.com> writes: > I have a question concerning the size of an index... > What I acually did was bulid a btree index on an smallint attribute > within a table with 10^8 rows. The table itself is app. 10GB large and > what I would like to have the smallest possible indeces. Unfortunately > the current size is about 2GB per indexed column (8 columns are indexed > in total) which is too large if the planner is supposed to choose a > bitmap scan between all of the indices. > So what I would like to know is the following: > Is there an easy way to tell postgres to occupy the index pages up to > 100 %? No, but even if there were it wouldn't make much of a difference. The minimum possible size of a PG index is about 16 bytes per entry, which would still put you at 1.6Gb for that many rows. regards, tom lane ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-02-05 00:49 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2006-02-02 14:03 Index occupancy tschak <jochen.schlosser@gmail.com> 2006-02-05 00:49 ` Tom Lane <tgl@sss.pgh.pa.us>
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