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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
