Re: A very long running query....

[Ioannis Anagnostopoulos <ioannis@xxxxxxxxxx>]

On 21/07/2012 00:10, Tom Lane wrote:

Claudio Freire <klaussfreire@xxxxxxxxx> 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

OK regarding the index I use... I follow your second advice about efficiency with individual columns and changed it to:

CREATE INDEX idx_message_copies_wk2_date_src_pos_partial
  ON feed_all_y2012m07.message_copies_wk2
  USING btree
  (date_trunc('day'::text, msg_date_rec), src_id, pos_georef1, pos_georef2, pos_georef3, pos_georef4)
TABLESPACE "index"
  WHERE
            pos_georef1 IS NOT NULL
            AND NOT pos_georef1::text = ''::text
            AND pos_georef2 IS NOT NULL
            AND NOT pos_georef2::text = ''::text
            AND pos_georef3 IS NOT NULL
            AND NOT pos_georef3::text = ''::text
            AND pos_georef4 IS NOT NULL
            AND NOT pos_georef4::text = ''::text;

The query has been changed as well as follows now:

SELECT
        src_id,
        date_trunc('day', message_copies.msg_date_rec) as date_count,
        message_copies.pos_georef1,
        message_copies.pos_georef2,
        message_copies.pos_georef3,
        message_copies.pos_georef4,
        ais_server.array_accum(CASE WHEN msg_type BETWEEN  1 and  3 THEN message_copies.msg_id END) as msgA_array,
        ais_server.array_accum(CASE WHEN msg_type = 18 THEN message_copies.msg_id END) as msgB_std_array,
        ais_server.array_accum(CASE WHEN msg_type = 19 THEN message_copies.msg_id END) as msgB_ext_array,
        uniq
        (
            ais_server.array_accum(CASE WHEN obj_type = 'SHIP_TYPE_A' THEN obj_mmsi END)
        ) as mmsi_type_A_array,
        uniq
        (
            ais_server.array_accum(CASE WHEN obj_type = 'SHIP_TYPE_B' THEN obj_mmsi END)
        ) as mmsi_type_B_array,
        avg(ship_speed) / 10.0 as avg_speed,
        avg(ship_heading) as avg_heading,
        avg(ship_course) / 10.0 as avg_course,
        ST_Multi(ST_Collect(ship_pos_messages.pos_point)) as geom
    from
        feed_all_y2012m07.message_copies join 
            (feed_all_y2012m07.ship_pos_messages join ais_server.ship_objects on (ship_pos_messages.obj_id = ship_objects.obj_id))
            on (message_copies.msg_id = ship_pos_messages.msg_id)
    where
        extract('day' from message_copies.msg_date_rec) = 17
        and date_trunc('day', message_copies.msg_date_rec) = '2012-07-17'
        and message_copies.src_id = 5
        and not message_copies.pos_georef1 = '' and not message_copies.pos_georef2 = '' and not message_copies.pos_georef3 = '' and not message_copies.pos_georef4 = ''
        and message_copies.pos_georef1 is not null and  message_copies.pos_georef2 is not null and message_copies.pos_georef3 is not null and message_copies.pos_georef4 is not null
        and extract('day' from ship_pos_messages.msg_date_rec) = 17
        group by src_id, date_count, message_copies.pos_georef1, message_copies.pos_georef2, message_copies.pos_georef3, message_copies.pos_georef4;

I am not sure that I can see an improvement, at least on src_id that have lots of msg_id per day the query never returned even 5 hours later running "exaplain analyze". For smaller src_id
(message wise) there might be some improvement or it was just the analyse that I run. As I said the stats goes quickly out of scope because of the big number of updates. So it looks like that
it is not the "funny" "where" concatenation or some kind of index construction problem. Which brings us back to the issue of the "statistics_target" on  per column. My problem is that given the
query plan I provided you yesterday, I am not sure which columns statistics_target to touch and what short of number to introduce. Is there any rule of thumb?

Kind regards
Yiannis