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-----