From: | Ozz Nixon <ozznixon(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | optimization (can I move pgsql_tmp)? |
Date: | 2010-10-05 19:02:38 |
Message-ID: | B840BB82-FC53-4ADF-9077-4450D35E905D@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 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
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2010-10-05 19:04:38 | Re: PG website testing |
Previous Message | Michael Gould | 2010-10-05 18:56:12 | Re: Problem installing 9.0.1 on Windows 7 x64 |