Good after everyone,
We are experiencing some performance issues on a table with 7 fields, 8,800,000 rows. During some exercises, one thing I noticed is I need to change the configuration of the system to house pgsql_tmp on a host partition instead of the drive 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
