optimization (can I move pgsql_tmp)? - Mailing list pgsql-general

Good after everyone,

    We are experiencing some performance issues on a table with 7 fields, 8,800,000 rows. During some exercises, one
thingI noticed is I need to change the configuration of the system to house pgsql_tmp on a host partition instead of
thedrive array... that will get me a little more speed... is this controlled via a .conf file or pgamin? 

    Optimization questions:

    When is pgsql_tmp used? (Right now as I am creating indexes by hand, I see it grow for about 10 minutes):

du -h /mnt/data/base/
5.1M    /mnt/data/base/1
5.1M    /mnt/data/base/11563
4.0G    /mnt/data/base/11564
8.9M    /mnt/data/base/16395
586M    /mnt/data/base/pgsql_tmp

    During the create index - communications in general to the drive array is "consumed".

    Before I keep experimenting and making things worse, I will ask - what indexes should I have to make this query
better- or how does on debug when they find a query is taking too long??? 

STRUCTURE:
   quer.SQL.Add('create table '+DBTags+' (');
   quer.SQL.Add('   pagename '+SQL_TITLE+'(100) not null,');
   quer.SQL.Add('   tagword '+SQL_TITLE+'(15) not null,');
   quer.SQL.Add('   soundex2 '+SQL_TITLE+'(4) not null,');
   quer.SQL.Add('   metaphone '+SQL_TITLE+'(15) not null,');
   quer.SQL.Add('   metaphone2 '+SQL_TITLE+'(22) not null,');
   quer.SQL.Add('   carverphone '+SQL_TITLE+'(22) not null,');
   quer.SQL.Add('   instances '+SQL_INT32+' not null,');
   if SQL_NAME_PRIMARY_KEYS then quer.SQL.Add('   constraint '+DBTags+'_PK');
   quer.SQL.Add('   primary key(pagename, tagword, instances)');


WAS 18 seconds with just the primary key, so I tried:
create index tags1 on allwikitags(tagword) -- after still 18 seconds

Then I tried:
create index tags6 on allwikitags(tagword,instances desc, pagename) -- after now 32 seconds


My Query:

select pagename,tagword,instances from allwikitags
where tagword in
('journey','journeys','journeyes','journeyd','journeyed','journeyly','journeyy','journeyth','journeydom','journeying','journeyize','journeyion','journeyism','journeyized','journeyizes','journeyizing','journeyions','journeyists','journeyfulness','journeyise','journeyish','journeyist','journeyful','journeytion','journeyless','journeyable','journeyfull','journeyious','journeylike','journeyment','journeyness')
or soundex2 in ('J650')
or metaphone in ('jrny')
or metaphone2 in ('JRN')
group by pagename,tagword,instances
order by tagword,instances desc,pagename

Thanks,
Ozz