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