Joseph S <[EMAIL PROTECTED]> writes:
> Actually sacode is an int2.
Ah. 8.2 is not very good at proving cross-type predicate conditions,
because it lacks the concept of an operator family. You need to declare
the index this way:
create index d2i on d2 (sgcode, sacode)
WHERE sacode IS NOT NULL
Joseph S <[EMAIL PROTECTED]> writes:
> Starting with a fresh database I got the same results you did, but not
> with my production table.
So, what's different between your table declaration and my toy example?
Can you force it to use the partial index by dropping the other index?
(Use begin; dro
Actually sacode is an int2. I didn't mention it before because I
already tried explicit casts and that didn't do anything. Now I just
realized that in your testcase you use int instead of int2. I just retried:
[local]:playpen=# create table d2(sgcode int, sacode int2);
CREATE TABLE
Time:
Joseph S <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Can you force it to use the partial index by dropping the other index?
>> (Use begin; drop index ...; explain ...; rollback; to avoid dropping
>> the index for real.) It's quite unclear at this point whether it
> I tried, and it ends up us
Tom Lane wrote:
Can you force it to use the partial index by dropping the other index?
(Use begin; drop index ...; explain ...; rollback; to avoid dropping
the index for real.) It's quite unclear at this point whether it
I tried, and it ends up using a seqscan.
--
Sent via pgsql-general ma
Creating an index without the IS NOT NULL did not help. The complete
version:
PostgreSQL 8.2.9 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.1.1 20070105 (Red Hat 4.1.1-52)
Starting with a fresh database I got the same results you did, but not
with my production table.
Tom Lan
Joseph S <[EMAIL PROTECTED]> writes:
> It seems that postgres can't figure out that it can use the index on
> sacode unless I put "d2.sacode > 0" in my where clause.
Works for me ...
regression=# create table d2(sgcode int, sacode int);
CREATE TABLE
regression=# create index d2i on d2 (sgcode, s
I have a table, d2, that has a field sacode that is almost always null.
In fact the stanullfrac in pg_statistic for this column is 1. I have
this index on my table:
"d2_sgcode_sacode_idx" btree (sgcode, sacode) WHERE sacode IS NOT NULL
AND sacode > 0
The first version of my query wasn't u
