Re: Query on postgresql 7.4.2 not using index

On Tue, 2006-04-25 at 10:47, Arnau wrote:
> Tom Lane wrote:
> > Arnau <arnaulist(at)andromeiberica(dot)com> writes:
> >
> >
> >>espsm_moviltelevision=# select count(*) from agenda_users_groups ;
> >> count
> >>---------
> >> 2547556
> >
> >
> > So the SELECT is fetching nearly 15% of the rows in the table. The
> > planner is doing *the right thing* to use a seqscan, at least for
> > this particular group_id value.
>
>
> 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.

> 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