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

-- create index tags1 on allwikitags(tagword) - after still 18 seconds
-- create index tags6 on allwikitags(tagword,instances desc, pagename) - after now 32 seconds
-- drop index tags6
-- create index tags2 on allwikitags(instances) - after still 18 seconds
-- create index tags3 on allwikitags(soundex2)
-- create index tags4 on allwikitags(metaphone)
-- create index tags5 on allwikitags(metaphone2) - after 3 now 1 second

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

    Giving it indexes for each of the "or" elements got the 8.8 million row query down to 1 second. So now, I just
awaitfor suggestions of how one would debug this and know he needed to hang more indexes off the table? 

Thanks,
Ozz
On Oct 5, 2010, at 3:02 PM, Ozz Nixon wrote:

> 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