Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 80CC3113F981 for ; Sat, 21 Jul 2012 16:16:53 -0300 (ADT) Received: from mail.anatec.com ([87.84.168.242]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1SsfAX-0000d5-ID for pgsql-performance@postgresql.org; Sat, 21 Jul 2012 19:16:52 +0000 Received: from [192.168.10.57] ([192.168.10.57]) by mail.anatec.com over TLS secured channel with Microsoft SMTPSVC(6.0.3790.4675); Sat, 21 Jul 2012 20:16:35 +0100 Message-ID: <500B0014.5070600@anatec.com> Date: Sat, 21 Jul 2012 20:16:36 +0100 From: Ioannis Anagnostopoulos User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:14.0) Gecko/20120713 Thunderbird/14.0 MIME-Version: 1.0 To: Tom Lane CC: Claudio Freire , pgsql-performance@postgresql.org Subject: Re: A very long running query.... References: <5007FC82.9070007@anatec.com> <5009CB5A.7070801@anatec.com> <5009CD43.8000300@anatec.com> <3639.1342825802@sss.pgh.pa.us> In-Reply-To: <3639.1342825802@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------000908010604020106070301" X-OriginalArrivalTime: 21 Jul 2012 19:16:36.0044 (UTC) FILETIME=[58B0F8C0:01CD6775] X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/257 X-Sequence-Number: 47438 This is a multi-part message in MIME format. --------------000908010604020106070301 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 21/07/2012 00:10, Tom Lane wrote: > 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 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 --------------000908010604020106070301 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 21/07/2012 00:10, Tom Lane wrote:
Claudio Freire <klaussfreire@gmail.com> 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

  


--------------000908010604020106070301--