Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id EF7AA9FB1BA
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 31 Oct 2006 00:21:10 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 81293-03 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 31 Oct 2006 04:21:05 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id 6E6759FB2F9
	for <pgsql-performance@postgresql.org>;
	Tue, 31 Oct 2006 00:21:02 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3
Received: from tanukisoftware.com (ns1.tanukisoftware.com [220.110.69.218])
	by svr4.postgresql.org (Postfix) with ESMTP id 55ADB5AF895
	for <pgsql-performance@postgresql.org>;
	Tue, 31 Oct 2006 04:04:18 +0000 (GMT)
Received: from [192.168.51.54] (210.131.212.148:62266)
	by tanukisoftware.com with [XMail 1.20 ESMTP Server]
	id <S22CDA> for <pgsql-performance@postgresql.org> from
	<leiflists@tanukisoftware.com>; Tue, 31 Oct 2006 13:04:12 +0900
Message-ID: <4546CB3C.4020206@tanukisoftware.com>
Date: Tue, 31 Oct 2006 13:04:12 +0900
From: Leif Mortenson <leiflists@tanukisoftware.com>
User-Agent: Thunderbird 1.5.0.7 (Windows/20060909)
MIME-Version: 1.0
To: Postgres Performance <pgsql-performance@postgresql.org>
Subject: Index ignored on column containing mostly 0 values
Content-Type: text/plain; charset=ISO-2022-JP
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200610/621
X-Sequence-Number: 21750

Hello,
I have been having a problem with the following query ignoring an index
on the foos.bar column.

SELECT c.id
FROM foos c, bars r
WHERE r.id != 0
AND r.modified_time > '2006-10-20 10:00:00.000'
AND r.modified_time <= '2006-10-30 15:20:00.000'
AND c.bar = r.id

The bars table contains 597 rows, while the foos table contains 5031203
rows.

After much research I figured out that the problem is being caused by the
PG planner deciding that my foos.bar index is not useful. The data in the
foos.bar column contains 5028698 0 values and 2505 that are ids in the bars
table.

Both tables have just been analyzed.

When I EXPLAIN ANALYZE the above query, I get the following:

"Hash Join (cost=3.06..201642.49 rows=25288 width=8) (actual
time=0.234..40025.514 rows=11 loops=1)"
" Hash Cond: ("outer".bar = "inner".id)"
" -> Seq Scan on foos c (cost=0.00..176225.03 rows=5032303 width=16)
(actual time=0.007..30838.623 rows=5031203 loops=1)"
" -> Hash (cost=3.06..3.06 rows=3 width=8) (actual time=0.117..0.117
rows=20 loops=1)"
" -> Index Scan using bars_index_modified_time on bars r
(cost=0.00..3.06 rows=3 width=8) (actual time=0.016..0.066 rows=20 loops=1)"
" Index Cond: ((modified_time > '2006-10-20 10:00:00'::timestamp without
time zone) AND (modified_time <= '2006-10-30 15:20:00'::timestamp
without time zone))"
" Filter: (id <> 0)"
"Total runtime: 40025.629 ms"

The solution I found was to change the statistics on my foos.bar column from
the default -1 to 1000. When I do this, reanalyze the table, and rerun
the above
query, I get the following expected result.

"Nested Loop (cost=0.00..25194.66 rows=25282 width=8) (actual
time=13.035..23.338 rows=11 loops=1)"
" -> Index Scan using bars_index_modified_time on bars r
(cost=0.00..3.06 rows=3 width=8) (actual time=0.063..0.115 rows=20 loops=1)"
" Index Cond: ((modified_time > '2006-10-20 10:00:00'::timestamp without
time zone) AND (modified_time <= '2006-10-30 15:20:00'::timestamp
without time zone))"
" Filter: (id <> 0)"
" -> Index Scan using foos_index_bar on foos c (cost=0.00..6824.95
rows=125780 width=16) (actual time=1.141..1.152 rows=1 loops=20)"
" Index Cond: (c.bar = "outer".id)"
"Total runtime: 23.446 ms"

Having to do this concerns me as I am not sure what a good statistics value
should be. Also we expect this table to grow much larger and I am concerned
that it may not continue to function correctly. I tried a value of 100
and that
works when the number of bars records is small, but as soon as I increase
them, the query starts ignoring the index again.

Is increasing the statistics value the best way to resolve this problem? How
can I best decide on a good statistics value?

Having a column containing large numbers of null or 0 values seems fairly
common. Is there way to tell Postgres to create an index of all values with
meaning. Ie all non-0 values? None that I could find.

Thanks in advance,
Leif