Thread: 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@sigaev.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@sigaev.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@sigaev.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@sigaev.ru
WWW: http://www.sigaev.ru/