Subject: Re: Query on postgresql 7.4.2 not using index
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: arnaulist@andromeiberica.com
Cc: pgsql-performance@postgresql.org
In-Reply-To: <444E44A2.6030703@andromeiberica.com>
References: <444E240D.3050000@andromeiberica.com>
	<3c1395330604250634r6f215ceame157f3dd5f5f4fa3@mail.gmail.com>
	<444E28ED.3090900@andromeiberica.com> <19483.1145978579@sss.pgh.pa.us>
	<444E44A2.6030703@andromeiberica.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1145980463.23538.218.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Tue, 25 Apr 2006 10:54:23 -0500

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