Subject: Re: Index ignored on column containing mostly 0 values
From: Andreas Kostyrka <andreas@kostyrka.org>
To: Leif Mortenson <leiflists@tanukisoftware.com>
Cc: Postgres Performance <pgsql-performance@postgresql.org>
In-Reply-To: <4546CB3C.4020206@tanukisoftware.com>
References: <4546CB3C.4020206@tanukisoftware.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-Y40oMFyfIpwD1jeO1Jke"
Date: Tue, 31 Oct 2006 16:03:55 +0100
Message-Id: <1162307035.18283.3.camel@andi-lap>
Mime-Version: 1.0


--=-Y40oMFyfIpwD1jeO1Jke
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

Am Dienstag, den 31.10.2006, 13:04 +0900 schrieb Leif Mortenson:
> Hello,
> I have been having a problem with the following query ignoring an index
> on the foos.bar column.
>=20
> SELECT c.id
> FROM foos c, bars r
> WHERE r.id !=3D 0
> AND r.modified_time > '2006-10-20 10:00:00.000'
> AND r.modified_time <=3D '2006-10-30 15:20:00.000'
> AND c.bar =3D r.id
>=20
> The bars table contains 597 rows, while the foos table contains 5031203
> rows.
>=20
> 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 ba=
rs
> table.
>=20
> Both tables have just been analyzed.
>=20
> When I EXPLAIN ANALYZE the above query, I get the following:
>=20
> "Hash Join (cost=3D3.06..201642.49 rows=3D25288 width=3D8) (actual
> time=3D0.234..40025.514 rows=3D11 loops=3D1)"
> " Hash Cond: ("outer".bar =3D "inner".id)"
> " -> Seq Scan on foos c (cost=3D0.00..176225.03 rows=3D5032303 width=3D16=
)
> (actual time=3D0.007..30838.623 rows=3D5031203 loops=3D1)"
> " -> Hash (cost=3D3.06..3.06 rows=3D3 width=3D8) (actual time=3D0.117..0.=
117
> rows=3D20 loops=3D1)"
> " -> Index Scan using bars_index_modified_time on bars r
> (cost=3D0.00..3.06 rows=3D3 width=3D8) (actual time=3D0.016..0.066 rows=
=3D20 loops=3D1)"
> " Index Cond: ((modified_time > '2006-10-20 10:00:00'::timestamp without
> time zone) AND (modified_time <=3D '2006-10-30 15:20:00'::timestamp
> without time zone))"
> " Filter: (id <> 0)"
> "Total runtime: 40025.629 ms"
>=20
> The solution I found was to change the statistics on my foos.bar column f=
rom
> the default -1 to 1000. When I do this, reanalyze the table, and rerun
> the above
> query, I get the following expected result.
>=20
> "Nested Loop (cost=3D0.00..25194.66 rows=3D25282 width=3D8) (actual
> time=3D13.035..23.338 rows=3D11 loops=3D1)"
> " -> Index Scan using bars_index_modified_time on bars r
> (cost=3D0.00..3.06 rows=3D3 width=3D8) (actual time=3D0.063..0.115 rows=
=3D20 loops=3D1)"
> " Index Cond: ((modified_time > '2006-10-20 10:00:00'::timestamp without
> time zone) AND (modified_time <=3D '2006-10-30 15:20:00'::timestamp
> without time zone))"
> " Filter: (id <> 0)"
> " -> Index Scan using foos_index_bar on foos c (cost=3D0.00..6824.95
> rows=3D125780 width=3D16) (actual time=3D1.141..1.152 rows=3D1 loops=3D20=
)"
> " Index Cond: (c.bar =3D "outer".id)"
> "Total runtime: 23.446 ms"
>=20
> Having to do this concerns me as I am not sure what a good statistics val=
ue
> should be. Also we expect this table to grow much larger and I am concern=
ed
> 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.
>=20
> Is increasing the statistics value the best way to resolve this problem? =
How
> can I best decide on a good statistics value?
>=20
> 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 wi=
th
> meaning. Ie all non-0 values? None that I could find.
Have you tried

CREATE INDEX partial ON foos (bar) WHERE bar IS NOT NULL;

Andreas


--=-Y40oMFyfIpwD1jeO1Jke
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: Dies ist ein digital signierter Nachrichtenteil

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQBFR2XaHJdudm4KnO0RAlBiAJoC65jVFwXS216atnRrRL5SHGOqGwCgigwH
FHYEes+vbNnmwbQ2OB+cFWw=
=YH9d
-----END PGP SIGNATURE-----

--=-Y40oMFyfIpwD1jeO1Jke--