Re: BIGINT indexes still with problems

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Dan Ruthers wrote:

| Now, if I run this query (note the int8 cast - also tried with the '' cast to String, same results):
| test=> explain select * from dmaildatum where idparent=int8(783219);
| QUERY PLAN
| ------------------------------------------------------------------
| Seq Scan on dmaildatum (cost=0.00..2241.71 rows=2229 width=272)
| Filter: (idparent = 783219::bigint)
| (2 rows)
|
| The index is not used. But with an identical query, only different parameter value:
| desknow=> explain select * from dmaildatum where idparent=int8(1187838);
| QUERY PLAN
|
| --------------------------------------------------------------------------------
| ---------------
| Index Scan using ix_dmaildatum_idparent on dmaildatum (cost=0.00..284.05 rows=
| 102 width=272)
| Index Cond: (idparent = 1187838::bigint)
| (2 rows)
|
| The index is used!
| I also did a vacuum analyze, and restarted Postgres and it did not make any difference.
| I tried many other ID values (ex 783218 and 783220), and they seem to use the index correctly. Only that value doesn't.
|
| Can anyone explain why Postgres behaves differently in these two cases, or at least point to some hints?

Because this means that a sequential scan is better for that value.

Perform this selects:

(1) select count(*) from dmaildatum;
(2) select count(*) from dmaildatum where idparent=int8(783219);
(3) select count(*) from dmaildatum where idparent=int8(1187838);

I bet that the ratio (2)/(1) is greater then (3)/(1).

Now show us the following results:

explain analyze select * from dmaildatum where idparent=int8(783219);
explain analyze select * from dmaildatum where idparent=int8(1187838);

and repeat it again but executing before:

set enable_seqscan = off;

Depending on the results that you get may be you need to lower the index
scan cost tuning the cpu related GUC variables.

Regards
Gaetano Mendola

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBGSfL7UpzwH2SGd4RAgBsAKCXvs2L/XUEmSGxBzEiAHmWasgShACeLvjp
9m12DSnj2tBuGSgldr4D9Po=
=KTil
-----END PGP SIGNATURE-----