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

Re: postgresql meltdown on PlanetMath.org

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 PlanetMath.org
Date: 2003-03-18 14:59:56
Message-ID: Pine.LNX.4.44.0303180647330.20661-100000@taurus.smoothcorp.com (view raw or flat)
Thread:
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:

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


In response to

pgsql-performance by date

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

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