Hello!
I have this table:
create table test (
s1 varchar(255),
s2 varchar(255),
i1 integer,
i2 integer,
... over 100 other fields
);
table contains over 8 million records
there's these indexes:
create index is1 on test (s1);
create index is2 on test (s2);
create index ii1 on test (i1);
create index ii2 on test (i2);
create index ii3 on test (i1, i2);
and then i run this query:
select
*
from (
select *
from test
where
is1 = 'aa' or is2 = 'aa'
)
where
is1 = 1
or (is1 = 1
and is2 = 1)
or (is1 = 2
and is2 = 2)
or (is1 = 3
and is2 = 3)
where part of outer query can have different count of
"or (is1 = N
and is2 = M)"
expressions, lets name this number X.
When X is low planner chooses index scan using is1 and is2,
then BitmapAnd that with index scan using ii1, ii2 or ii3.
But when X is big enough (> 15) planner chooses seqscan and filter on
i1, i2, s1, s2.
Seqscan is very slow and I want to avoid it. Subquery is very fast
and i don't know why postgres chooses that plan.
I know I can set enable_seqscan = off.
Is there other ways to enforce index usage?
postgres pg_class have right estimate of rowcount.
--
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing in e-mail?
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
