I have done the same tests on 8.1.0.
espsm_moviltelevision=# EXPLAIN ANALYZE SELECT agenda_user_group_id FROM
agenda_users_groups WHERE group_id = 9;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on agenda_users_groups (cost=2722.26..30341.78
rows=400361 width=8) (actual time=145.533..680.839 rows=367026 loops=1)
Recheck Cond: (group_id = 9::numeric)
-> Bitmap Index Scan on i_agnusrsgrs_groupid (cost=0.00..2722.26
rows=400361 width=0) (actual time=142.958..142.958 rows=367026 loops=1)
Index Cond: (group_id = 9::numeric)
Total runtime: 1004.966 ms
(5 rows)
How big are these individual records? I'm guessing a fairly good size,
since an index scan is winning.
How I could know the size on an individual record?
espsm_moviltelevision=# EXPLAIN ANALYZE SELECT agenda_user_group_id FROM
agenda_users_groups WHERE group_id::int8 = 9;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Seq Scan on agenda_users_groups (cost=0.00..60947.43 rows=12777
width=8) (actual time=457.963..2244.928 rows=367026 loops=1)
Filter: ((group_id)::bigint = 9)
Total runtime: 2571.496 ms
(3 rows)
OK. Stop and think about what you're telling postgresql to do here.
You're telling it to cast the field group_id to int8, then compare it to
9. How can it cast the group_id to int8 without fetching it? That's
right, you're ensuring a seq scan. You need to put the int8 cast on the
other side of that equality comparison, like:
where group_id = 9::int8
I just did what Chris Smith asked me to do :), here I paste the
results I get when I change the cast.
espsm_moviltelevision=# EXPLAIN ANALYZE SELECT agenda_user_group_id FROM
agenda_users_groups WHERE group_id = 9::int8;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on agenda_users_groups (cost=2722.33..30343.06
rows=400379 width=8) (actual time=147.723..714.473 rows=367026 loops=1)
Recheck Cond: (group_id = 9::numeric)
-> Bitmap Index Scan on i_agnusrsgrs_groupid (cost=0.00..2722.33
rows=400379 width=0) (actual time=145.015..145.015 rows=367026 loops=1)
Index Cond: (group_id = 9::numeric)
Total runtime: 1038.537 ms
(5 rows)
espsm_moviltelevision=# EXPLAIN ANALYZE SELECT agenda_user_group_id FROM
agenda_users_groups WHERE group_id = '9'::int8;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on agenda_users_groups (cost=2722.33..30343.06
rows=400379 width=8) (actual time=153.858..1192.838 rows=367026 loops=1)
Recheck Cond: (group_id = 9::numeric)
-> Bitmap Index Scan on i_agnusrsgrs_groupid (cost=0.00..2722.33
rows=400379 width=0) (actual time=151.298..151.298 rows=367026 loops=1)
Index Cond: (group_id = 9::numeric)
Total runtime: 1527.039 ms
(5 rows)
Thanks
--
Arnau
