Re: how could select id=xx so slow?

От: Albe Laurenz
Тема: Re: how could select id=xx so slow?
Дата: ,
Msg-id: D960CB61B694CF459DCFB4B0128514C2081BF021@exadv11.host.magwien.gv.at
(см: обсуждение, исходный текст)
Ответ на: how could select id=xx so slow?  (Yan Chunlu)
Ответы: Re: how could select id=xx so slow?  (Yan Chunlu)
Список: pgsql-performance

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

how could select id=xx so slow?  (Yan Chunlu, )
 Re: how could select id=xx so slow?  (Daniel Farina, )
 Re: how could select id=xx so slow?  (Craig Ringer, )
 Re: how could select id=xx so slow?  ("Albe Laurenz", )
  Re: how could select id=xx so slow?  (Yan Chunlu, )
   Re: how could select id=xx so slow?  (Craig Ringer, )
    Re: how could select id=xx so slow?  (Yan Chunlu, )
 Re: how could select id=xx so slow?  (Craig Ringer, )
  Re: how could select id=xx so slow?  (Yan Chunlu, )
   Re: how could select id=xx so slow?  ("Albe Laurenz", )
    Re: how could select id=xx so slow?  (Yan Chunlu, )
     Re: how could select id=xx so slow?  (Craig Ringer, )
      Re: how could select id=xx so slow?  (Yan Chunlu, )
       Re: how could select id=xx so slow?  (Maciek Sakrejda, )
       Re: how could select id=xx so slow?  (Craig Ringer, )
        Re: how could select id=xx so slow?  (Yan Chunlu, )
         Re: how could select id=xx so slow?  (Craig Ringer, )
          Re: how could select id=xx so slow?  (Yan Chunlu, )
           Re: how could select id=xx so slow?  (Craig Ringer, )
           Re: how could select id=xx so slow?  (Ants Aasma, )
            Re: how could select id=xx so slow?  (Jeff Janes, )
             Re: how could select id=xx so slow?  (Yan Chunlu, )
   Re: how could select id=xx so slow?  (Ants Aasma, )
    Re: how could select id=xx so slow?  (Yan Chunlu, )
  Re: how could select id=xx so slow?  (Yan Chunlu, )

Yan Chunlu wrote:
> I have grabbed one day slow query log and analyzed it by pgfouine, to
my surprise, the slowest query
> is just a simple select statement:
>
> select diggcontent_data_message.thing_id,
diggcontent_data_message.KEY,
> diggcontent_data_message.value, diggcontent_data_message.kind FROM
diggcontent_data_message WHERE
> diggcontent_data_message.thing_id = 3570882;
>
> where thing_id is the primary key,  guess how long it takes?
>
> 754.61 seconds!!
>
> I tried explain analyze it and below is the result, which is very
fast:
>
> explain analyze select diggcontent_data_message.thing_id,
diggcontent_data_message.KEY,
> diggcontent_data_message.value, diggcontent_data_message.kind FROM
diggcontent_data_message WHERE
> diggcontent_data_message.thing_id = 3570882;
>
QUERY PLAN
>
------------------------------------------------------------------------
------------------------------
> -------------------------------------------------------------
>  Index Scan using idx_thing_id_diggcontent_data_message on
diggcontent_data_message  (cost=0.00..15.34
> rows=32 width=51) (actual time=0.080..0.096 rows=8 loops=1)
>    Index Cond: (thing_id = 3570882)
>  Total runtime: 0.115 ms
> (3 rows)
>
> so I wonder could this simple select is innocent and affected badly by
other queries? how could I find
> those queries that really slow down the database?

Are these by any chance the aggregated costs in pgFouine?
Could it be that the statement just ran very often and used that time in
total?

Other than that, it could have been blocked by something that takes an
exclusive lock on the table.

There are no ON SELECT DO INSTEAD rules or similar things on the table,
right?

Yours,
Laurenz Albe


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

От: Chris Hanks
Дата:
Сообщение: Re: PostgreSQL db, 30 tables with number of rows < 100 (not huge) - the fastest way to clean each non-empty table and reset unique identifier column of empty ones.
От: Craig Ringer
Дата:
Сообщение: Re: Create tables performance