Lists: | pgsql-hackers |
---|
From: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> |
---|---|
To: | Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | TSearch2 performance issue? |
Date: | 2005-03-31 03:57:29 |
Message-ID: | 424B7529.3030807@familyhealth.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
I see this in my PQA analyzed PostgreSQL log:
######## Slowest queries
select dict_init, dict_initoption, dict_lexize from pg_ts_dict where oid
= $1
It's my number one slowest query apparently!
Can that lookup perhaps be cached in some way?
I notice that there is no unique index on the oid column, but that
shouldn't matter for performance since there are only like 6 rows in
that table.
Also, will this work with default_with_oids = false? (When the schema
is initialised.)
Chris
From: | Teodor Sigaev <teodor(at)sigaev(dot)ru> |
---|---|
To: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> |
Cc: | Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: TSearch2 performance issue? |
Date: | 2005-03-31 10:46:43 |
Message-ID: | 424BD513.4070502@sigaev.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Christopher Kings-Lynne wrote:
> I see this in my PQA analyzed PostgreSQL log:
>
> ######## Slowest queries
> select dict_init, dict_initoption, dict_lexize from pg_ts_dict where oid
> = $1
>
> It's my number one slowest query apparently!
>
> Can that lookup perhaps be cached in some way?
It's cached. This select should run only one time per connection for each used
dictionary. If its'not then it's a bug. I'll check it.
>
> I notice that there is no unique index on the oid column, but that
> shouldn't matter for performance since there are only like 6 rows in
> that table.
>
> Also, will this work with default_with_oids = false? (When the schema
> is initialised.)
All pg_ts_* tables are created with 'with oids' option.
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Teodor Sigaev E-mail: teodor(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/
From: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> |
---|---|
To: | Teodor Sigaev <teodor(at)sigaev(dot)ru> |
Cc: | Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: TSearch2 performance issue? |
Date: | 2005-03-31 11:07:20 |
Message-ID: | 424BD9E8.6030006@familyhealth.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
> It's cached. This select should run only one time per connection for
> each used dictionary. If its'not then it's a bug. I'll check it.
It probably is then - although I do use a persistent connection pool,
but I wouldn't have thought that'd use more than a new connection every
once in a while?
Chris
From: | Teodor Sigaev <teodor(at)sigaev(dot)ru> |
---|---|
To: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> |
Cc: | Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: TSearch2 performance issue? |
Date: | 2005-03-31 16:02:19 |
Message-ID: | 424C1F0B.6090705@sigaev.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
I found several unpleasant blot in comparing functions and commit changes to
7.4, 8.0 and head. Pls check (it need just to recompile .so file)
Christopher Kings-Lynne wrote:
>> It's cached. This select should run only one time per connection for
>> each used dictionary. If its'not then it's a bug. I'll check it.
>
>
> It probably is then - although I do use a persistent connection pool,
> but I wouldn't have thought that'd use more than a new connection every
> once in a while?
>
> Chris
--
Teodor Sigaev E-mail: teodor(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/
From: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> |
---|---|
To: | Teodor Sigaev <teodor(at)sigaev(dot)ru> |
Cc: | Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: TSearch2 performance issue? |
Date: | 2005-04-01 15:58:45 |
Message-ID: | 424D6FB5.8010608@familyhealth.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
It's a bit tricky, since the machine I noticed it on is in production -
how would I test this on a test machine with little data?
Should I put the 8.0 tsearch2.so on my 7.4 production server?
Chris
Teodor Sigaev wrote:
> I found several unpleasant blot in comparing functions and commit
> changes to 7.4, 8.0 and head. Pls check (it need just to recompile .so
> file)
>
> Christopher Kings-Lynne wrote:
>
>>> It's cached. This select should run only one time per connection for
>>> each used dictionary. If its'not then it's a bug. I'll check it.
>>
>>
>>
>> It probably is then - although I do use a persistent connection pool,
>> but I wouldn't have thought that'd use more than a new connection
>> every once in a while?
>>
>> Chris
>
>
From: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> |
---|---|
To: | Teodor Sigaev <teodor(at)sigaev(dot)ru> |
Cc: | Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: TSearch2 performance issue? |
Date: | 2005-04-04 01:42:34 |
Message-ID: | 42509B8A.4000005@familyhealth.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Hi Teodor,
What exactly did you fix here?
Chris
Teodor Sigaev wrote:
> I found several unpleasant blot in comparing functions and commit
> changes to 7.4, 8.0 and head. Pls check (it need just to recompile .so
> file)
>
> Christopher Kings-Lynne wrote:
>
>>> It's cached. This select should run only one time per connection for
>>> each used dictionary. If its'not then it's a bug. I'll check it.
>>
>>
>>
>> It probably is then - although I do use a persistent connection pool,
>> but I wouldn't have thought that'd use more than a new connection
>> every once in a while?
>>
>> Chris
>
>
From: | Teodor Sigaev <teodor(at)sigaev(dot)ru> |
---|---|
To: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> |
Cc: | Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: TSearch2 performance issue? |
Date: | 2005-04-04 07:12:36 |
Message-ID: | 4250E8E4.8090709@sigaev.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
No, you can't put *.so from 8.0 to 7.4. Get 7.4 sources from cvs and compile
contrib/tsearch2.
Christopher Kings-Lynne wrote:
> It's a bit tricky, since the machine I noticed it on is in production -
> how would I test this on a test machine with little data?
>
> Should I put the 8.0 tsearch2.so on my 7.4 production server?
>
> Chris
>
> Teodor Sigaev wrote:
>
>> I found several unpleasant blot in comparing functions and commit
>> changes to 7.4, 8.0 and head. Pls check (it need just to recompile .so
>> file)
>>
>> Christopher Kings-Lynne wrote:
>>
>>>> It's cached. This select should run only one time per connection for
>>>> each used dictionary. If its'not then it's a bug. I'll check it.
>>>
>>>
>>>
>>>
>>> It probably is then - although I do use a persistent connection pool,
>>> but I wouldn't have thought that'd use more than a new connection
>>> every once in a while?
>>>
>>> Chris
>>
>>
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Teodor Sigaev E-mail: teodor(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/
From: | Teodor Sigaev <teodor(at)sigaev(dot)ru> |
---|---|
To: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> |
Cc: | Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: TSearch2 performance issue? |
Date: | 2005-04-04 07:15:52 |
Message-ID: | 4250E9A8.5090105@sigaev.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Some thing like instead of
static int
comparedict(const void *a, const void *b)
{
return ((DictInfo *) a)->dict_id - ((DictInfo *) b)->dict_id;
}
now used
static int
comparedict(const void *a, const void *b)
{
if ( ((DictInfo *) a)->dict_id == ((DictInfo *) b)->dict_id )
return 0;
return ( ((DictInfo *) a)->dict_id < ((DictInfo *) b)->dict_id ) ? -1 : 1;
}
It works for small dict_id ( dict_id has type Oid ), but it was errnous for
dict_id>2^31.
Christopher Kings-Lynne wrote:
> Hi Teodor,
>
> What exactly did you fix here?
>
> Chris
>
> Teodor Sigaev wrote:
>
>> I found several unpleasant blot in comparing functions and commit
>> changes to 7.4, 8.0 and head. Pls check (it need just to recompile .so
>> file)
>>
>> Christopher Kings-Lynne wrote:
>>
>>>> It's cached. This select should run only one time per connection for
>>>> each used dictionary. If its'not then it's a bug. I'll check it.
>>>
>>>
>>>
>>>
>>> It probably is then - although I do use a persistent connection pool,
>>> but I wouldn't have thought that'd use more than a new connection
>>> every once in a while?
>>>
>>> Chris
>>
>>
>>
--
Teodor Sigaev E-mail: teodor(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/