-----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-----
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
