TSearch2 performance issue?

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

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/

> 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

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/

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
>
>

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
>
>

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/

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/