Re: Query on postgresql 7.4.2 not using index

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