On Tue, 2006-04-25 at 10:47, Arnau wrote: > Tom Lane wrote: > > Arnau <arnaulist@xxxxxxxxxxxxxxxxxx> 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
