Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 9B01A1C0B19A for ; Fri, 20 Jul 2012 20:10:27 -0300 (ADT) Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SsML2-0003Al-KR for pgsql-performance@postgresql.org; Fri, 20 Jul 2012 23:10:27 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q6KNA3rb003640; Fri, 20 Jul 2012 19:10:03 -0400 (EDT) From: Tom Lane To: Claudio Freire cc: Ioannis Anagnostopoulos , pgsql-performance@postgresql.org Subject: Re: A very long running query.... In-reply-to: References: <5007FC82.9070007@anatec.com> <5009CB5A.7070801@anatec.com> <5009CD43.8000300@anatec.com> Comments: In-reply-to Claudio Freire message dated "Fri, 20 Jul 2012 18:33:58 -0300" Date: Fri, 20 Jul 2012 19:10:02 -0400 Message-ID: <3639.1342825802@sss.pgh.pa.us> X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/249 X-Sequence-Number: 47430 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