Re: Strangely Variable Query Performance

От: Steve
Тема: Re: Strangely Variable Query Performance
Дата: ,
Msg-id: Pine.GSO.4.64.0704121757060.17955@kittyhawk.tanabi.org
(см: обсуждение, исходный текст)
Ответ на: Re: Strangely Variable Query Performance  (Tom Lane)
Ответы: Re: Strangely Variable Query Performance  (Tom Lane)
Список: pgsql-performance

Скрыть дерево обсуждения

Slow Postgresql server  (Jason Lustig, )
 Re: Slow Postgresql server  (Dennis Bjorklund, )
 Re: Slow Postgresql server  (Jeff Frost, )
 Re: Slow Postgresql server  (Ron, )
  Re: Slow Postgresql server  (Guido Neitzer, )
   Re: Slow Postgresql server  (Ron, )
    Re: Slow Postgresql server  (Guido Neitzer, )
     Re: Slow Postgresql server  (Scott Marlowe, )
      Re: Slow Postgresql server  (Jeff Frost, )
       Re: Slow Postgresql server  (Carlos Moreno, )
       Strangely Variable Query Performance  (Steve, )
        Re: Strangely Variable Query Performance  (Tom Lane, )
         Re: Strangely Variable Query Performance  (Steve, )
          Re: Strangely Variable Query Performance  (Tom Lane, )
           Re: Strangely Variable Query Performance  (Steve, )
            Re: Strangely Variable Query Performance  (Tom Lane, )
             Re: Strangely Variable Query Performance  (Steve, )
        Re: Strangely Variable Query Performance  (Scott Marlowe, )
         Re: Strangely Variable Query Performance  (Steve, )
          Re: Strangely Variable Query Performance  (Scott Marlowe, )
           Re: Strangely Variable Query Performance  (Steve, )
           Re: Strangely Variable Query Performance  (Tom Lane, )
            Re: Strangely Variable Query Performance  (Steve, )
             Re: Strangely Variable Query Performance  (Tom Lane, )
              Re: Strangely Variable Query Performance  (Steve, )
             Re: Strangely Variable Query Performance  (Tom Lane, )
              Re: Strangely Variable Query Performance  (Steve, )
               Re: Strangely Variable Query Performance  (Tom Lane, )
                Re: Strangely Variable Query Performance  (Steve, )
                 Re: Strangely Variable Query Performance  (Tom Lane, )
                  Re: Strangely Variable Query Performance  (Steve, )
                   Re: Strangely Variable Query Performance  (Tom Lane, )
                   Re: Strangely Variable Query Performance  (Tom Lane, )
                    Re: Strangely Variable Query Performance  (Steve, )
                     Re: Strangely Variable Query Performance  (Tom Lane, )
                      Fwd: Strangely Variable Query Performance  ("Robins Tharakan", )
                     choose_bitmap_and again (was Re: Strangely Variable Query Performance)  (Tom Lane, )
                      Re: [HACKERS] choose_bitmap_and again (was Re: Strangely Variable Query Performance)  (Alvaro Herrera, )
                       Re: [HACKERS] choose_bitmap_and again (was Re: Strangely Variable Query Performance)  (Tom Lane, )
                       Re: [HACKERS] choose_bitmap_and again (was Re: Strangely Variable Query Performance)  (Tom Lane, )
              Re: Strangely Variable Query Performance  (Steve, )
             Re: [HACKERS] choose_bitmap_and again (was Re: Strangely Variable Query Performance)  (Tom Lane, )
 Re: Slow Postgresql server  (Jeff Frost, )
  Re: Slow Postgresql server  (Jason Lustig, )
   Re: Slow Postgresql server  (Guido Neitzer, )
 Fwd: Strangely Variable Query Performance  (Robins, )
  Re: Fwd: Strangely Variable Query Performance  (Tom Lane, )

>
> Oy vey ... I hope this is a read-mostly table, because having that many
> indexes has got to be killing your insert/update performance.

     Hahaha yeah these are read-only tables.  Nightly inserts/updates.
Takes a few hours, depending on how many records (between 4 and 10
usually).  But during the day, while querying, read only.

> I see that some of the revop indexes might be considered relevant to
> this query, so how exactly have you got those opclasses defined?
> There's built-in support for reverse sort as of CVS HEAD, but in
> existing releases you must have cobbled something together, and I wonder
> if that could be a contributing factor ...

Here's the revops (the c functions are at the bottom):

CREATE FUNCTION ddd_date_revcmp(date, date) RETURNS integer
     AS '/usr/local/pgsql/contrib/cmplib.so', 'ddd_date_revcmp'
     LANGUAGE c STRICT;

CREATE FUNCTION ddd_int_revcmp(integer, integer) RETURNS integer
     AS '/usr/local/pgsql/contrib/cmplib.so', 'ddd_int_revcmp'
     LANGUAGE c STRICT;

CREATE FUNCTION ddd_text_revcmp(text, text) RETURNS integer
     AS '/usr/local/pgsql/contrib/cmplib.so', 'ddd_text_revcmp'
     LANGUAGE c STRICT;

CREATE OPERATOR CLASS date_revop
     FOR TYPE date USING btree AS
     OPERATOR 1 >(date,date) ,
     OPERATOR 2 >=(date,date) ,
     OPERATOR 3 =(date,date) ,
     OPERATOR 4 <=(date,date) ,
     OPERATOR 5 <(date,date) ,
     FUNCTION 1 ddd_date_revcmp(date,date);

CREATE OPERATOR CLASS int4_revop
     FOR TYPE integer USING btree AS
     OPERATOR 1 >(integer,integer) ,
     OPERATOR 2 >=(integer,integer) ,
     OPERATOR 3 =(integer,integer) ,
     OPERATOR 4 <=(integer,integer) ,
     OPERATOR 5 <(integer,integer) ,
     FUNCTION 1 ddd_int_revcmp(integer,integer);

CREATE OPERATOR CLASS text_revop
     FOR TYPE text USING btree AS
     OPERATOR 1 >(text,text) ,
     OPERATOR 2 >=(text,text) ,
     OPERATOR 3 =(text,text) ,
     OPERATOR 4 <=(text,text) ,
     OPERATOR 5 <(text,text) ,
     FUNCTION 1 ddd_text_revcmp(text,text);

Datum   ddd_date_revcmp(PG_FUNCTION_ARGS){
         DateADT         arg1=PG_GETARG_DATEADT(0);
         DateADT         arg2=PG_GETARG_DATEADT(1);

         PG_RETURN_INT32(arg2 - arg1);
}


Datum   ddd_int_revcmp(PG_FUNCTION_ARGS){
         int32           arg1=PG_GETARG_INT32(0);
         int32           arg2=PG_GETARG_INT32(1);

         PG_RETURN_INT32(arg2 - arg1);
}

Datum   ddd_text_revcmp(PG_FUNCTION_ARGS){
         char*           arg1=(char*)VARDATA(PG_GETARG_TEXT_P(0));
         char*           arg2=(char*)VARDATA(PG_GETARG_TEXT_P(1));

         if((*arg1) != (*arg2)){
                 PG_RETURN_INT32(*arg2 - *arg1);
         }else{
                 PG_RETURN_INT32(strcmp(arg2,arg1));
         }
}




В списке pgsql-performance по дате сообщения:

От: Steve
Дата:
Сообщение: Re: Question about memory allocations
От: Tom Lane
Дата:
Сообщение: Re: Strangely Variable Query Performance