Message-ID: <4D834921.6060705@thl.fi>
Date: Fri, 18 Mar 2011 13:59:29 +0200
From: =?UTF-8?B?QW5zc2kgS8Okw6RyacOkaW5lbg==?= <anssi.kaariainen@thl.fi>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.14) Gecko/20110223 Thunderbird/3.1.8
MIME-Version: 1.0
To: Vitalii Tymchyshyn <tivv00@gmail.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Disabling nested loops - worst case performance
References: <4D8306A7.7020205@thl.fi> <4D833953.7080607@gmail.com>
In-Reply-To: <4D833953.7080607@gmail.com>
Content-Type: text/plain; charset="UTF-8"; format=flowed
Content-Transfer-Encoding: 7bit

On 03/18/2011 12:52 PM, Vitalii Tymchyshyn wrote:
> If your queries work on single attribute, you can try adding partial
> indexes for different attributes. Note that in this case parameterized
> statements may prevent index usage, so check also with attribute id inlined.
>
> Best regards, Vitalii Tymchyshyn

Unfortunately this does not help for the statistics, and (I guess) 
nested loops will still be used when joining:

hot2=> explain analyze select * from attr_value where attr_tunniste = 
'suhde_hyvaksytty' and arvo_text = 't';
                                                                   QUERY 
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
  Index Scan using attr_value_arvo_text_idx1 on attr_value  
(cost=0.00..343.59 rows=152 width=118) (actual time=0.076..7.768 
rows=3096 loops=1)
    Index Cond: (arvo_text = 't'::text)
    Filter: ((attr_tunniste)::text = 'suhde_hyvaksytty'::text)
  Total runtime: 10.855 ms
(4 rows)

hot2=> create index suhde_hyvaksytty_idx on attr_value(arvo_text) where 
attr_tunniste = 'suhde_hyvaksytty';
CREATE INDEX
hot2=> analyze attr_value;
hot2=> explain analyze select * from attr_value where attr_tunniste = 
'suhde_hyvaksytty' and arvo_text = 't';
                                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
  Index Scan using suhde_hyvaksytty_idx on attr_value  (cost=0.00..43.72 
rows=152 width=118) (actual time=0.093..4.776 rows=3096 loops=1)
    Index Cond: (arvo_text = 't'::text)
  Total runtime: 7.817 ms
(3 rows)

  - Anssi