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

Re: postgresql meltdown on

From: Chris Sutton <chris(at)smoothcorp(dot)com>
To: Logan Bowers <logan(at)datacurrent(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: postgresql meltdown on
Date: 2003-03-18 14:59:56
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
On Mon, 17 Mar 2003, Logan Bowers wrote:

> Logan Bowers 
> \d tblfiles: (219,248 rows)
>   Column  |            Type             |                 Modifiers
> ----------+-----------------------------+-------------------------------------------
>  fid      | integer                     | not null default 
> nextval('fileids'::text)
>  hid      | integer                     | not null
>  pid      | integer                     | not null
>  name     | character varying(256)      | not null
>  size     | bigint                      | not null
> Indexes: temp_fid_key unique btree (fid),
>          filediridx btree (hid, pid, name, size, fid),
>          fileidx btree (name, hid, pid, fid),
>          fileidxfid btree (fid, name, pid)

I'm no expert on indexes, but I seem to remember reading that creating 
multicolumn indexes on more than 2 or 3 columns gets sort of pointless:

There is probably a ton of disk space and CPU used to keep all these multi 
column indexes.  Might be part of the problem.

> \d tblwordidx: (1,739,481 rows)
>           Table "public.tblwordidx"
>  Column |          Type          | Modifiers
> --------+------------------------+-----------
>  fid    | integer                | not null
>  word   | character varying(128) | not null
>  todel  | boolean                |
> Indexes: wordidxfid btree (fid, word),
>          wordidxfidonly btree (fid),
>          wordidxw btree (word, fid)

Another index question for the pros.  When creating a multi-column index 
do you need to do it both ways:

wordidxfid btree (fid, word)
wordidxw btree (word, fid

We have a very similar "dictonary" table here for searching.  It's about 
1.7 million rows, takes about 80mb of disk space.  There is one multi 
column index on the table which uses about 50mb of disk space.

To find out how much disk space you are using, the hard way is:

select relfilenode from pg_class where relname='tblwordidx';
select relfilenode from pg_class where relname='wordidxw';

relfilenode is the name of the file in your data directory.

I'm pretty sure there is an easier way to do this with a function I saw in 

Just some thoughts.


In response to

pgsql-performance by date

Next:From: Kendrick C. WilsonDate: 2003-03-18 15:19:44
Subject: Re: postgresql meltdown on
Previous:From: Tom LaneDate: 2003-03-18 02:51:49
Subject: Re: postgresql meltdown on

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