Re: A very long running query....

От: Tom Lane
Тема: Re: A very long running query....
Дата: ,
Msg-id: 3639.1342825802@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: A very long running query....  (Claudio Freire)
Ответы: Re: A very long running query....  (Ioannis Anagnostopoulos)
Re: A very long running query....  (Ioannis Anagnostopoulos)
Список: pgsql-performance

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

A very long running query....  (Ioannis Anagnostopoulos, )
 Re: A very long running query....  (Claudio Freire, )
  Re: A very long running query....  (Ioannis Anagnostopoulos, )
   Re: A very long running query....  (Rosser Schwarz, )
    Re: A very long running query....  (Ioannis Anagnostopoulos, )
     Re: A very long running query....  (Ioannis Anagnostopoulos, )
      Re: A very long running query....  (Craig Ringer, )
   Re: A very long running query....  (Claudio Freire, )
    Re: A very long running query....  (Ioannis Anagnostopoulos, )
    Re: A very long running query....  (Tom Lane, )
     Re: A very long running query....  (Ioannis Anagnostopoulos, )
     Re: A very long running query....  (Ioannis Anagnostopoulos, )
      Re: A very long running query....  (Claudio Freire, )
       Re: A very long running query....  (Ioannis Anagnostopoulos, )
 Re: A very long running query....  ("Marc Mamin", )
  Re: A very long running query....  (Ioannis Anagnostopoulos, )
   Re: A very long running query....  (Tom Lane, )
    Re: A very long running query....  (Ioannis Anagnostopoulos, )
 Re: A very long running query....  (Claudio Freire, )
  Re: A very long running query....  (Claudio Freire, )
   Re: A very long running query....  (Ioannis Anagnostopoulos, )

Claudio Freire <> writes:
> Looking at this:

> "                    ->  Index Scan using
> idx_message_copies_wk2_date_src_pos_partial on message_copies_wk2
> message_copies  (cost=0.00..19057.93 rows=52 width=32) (actual
> time=62.124..5486270.845 rows=387524 loops=1)"
> "                          Index Cond: ((date_trunc('day'::text,
> msg_date_rec) = '2012-07-17 00:00:00'::timestamp without time zone)
> AND (src_id = 1))"
> "                          Filter: ((date_part('day'::text,
> msg_date_rec) = 17::double precision) AND (NOT (((((pos_georef1)::text
> || (pos_georef2)::text) || (pos_georef3)::text) ||
> (pos_georef4)::text) IS NULL)) AND (((((pos_georef1)::text ||
> (pos_georef2)::text) || (pos_georef3)::text) || (pos_georef4)::text)
> <> ''::text))"

I think the real problem is that the planner has no hope of doing
anything very accurate with such an unwieldy filter condition.  I'd look
at ways of making the filter conditions simpler, perhaps by recasting
the data representation.  In particular, that's a horridly bad way of
asking whether some columns are empty, which I gather is the intent.
If you really want to do it just like that, creating an index on the
concatenation expression would guide ANALYZE to collect some stats about
it, but it would probably be a lot more efficient to put together an AND
or OR of tests on the individual columns.

            regards, tom lane


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

От: Ioannis Anagnostopoulos
Дата:
Сообщение: Re: A very long running query....
От: Craig Ringer
Дата:
Сообщение: Re: A very long running query....