Subject: Re: Very slow query (3-4mn) on a table with 25millions rows
To: Abadie Lana <Lana.Abadie@iter.org>, Tom Lane <tgl@sss.pgh.pa.us>
References: <E544BB9A64ABD24DA201745FD316D94551122DDA@XCH2.iter.org>
 <4248.1469470017@sss.pgh.pa.us>
 <E544BB9A64ABD24DA201745FD316D94551124F74@XCH2.iter.org>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
From: =?UTF-8?Q?Mart=c3=adn_Marqu=c3=a9s?= <martin@2ndquadrant.com>
Message-ID: <24ade69a-70bf-4cb1-75ae-03a5481f067a@2ndquadrant.com>
Date: Tue, 26 Jul 2016 07:34:17 -0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.1.1
MIME-Version: 1.0
In-Reply-To: <E544BB9A64ABD24DA201745FD316D94551124F74@XCH2.iter.org>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

El 26/07/16 a las 06:01, Abadie Lana escribió:
> Hi Tom,
> Thanks for the hints..
> 
> I made various tests for index
> The best I could get is the following one with 
> create index vat_funcvaratt_multi_idx on functionalvarattributes(split_part(split_part(attvalue,' ',1),'.',1), tag_id, atttype_id);
> analyze functionalvarattributes;

I suggest running analyze over the other tables involved in the query
(or over the whole DB) and then sending back the explain analyze, or
even better EXPLAIN (ANALYZE,BUFFERS).

Some estimates are close and others are really wrong.

I'm not saying that's going to give you a big bust but we'll be able to
see the planner with fresh stats

-- 
Martín Marqués                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance