Re: postgresql meltdown on PlanetMath.org

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:

http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/indexes-multicolumn.html

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
contrib.

Just some thoughts.

Chris