From: Scott Marlowe <smarlowe@g2switchworks.com>
To: arnaulist@andromeiberica.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Query on postgresql 7.4.2 not using index
Date: Tue, 25 Apr 2006 10:54:23 -0500
Message-ID: <1145980463.23538.218.camel@state.g2switchworks.com> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: smarlowe@g2switchworks.com, arnaulist@andromeiberica.com
Subject: Re: Query on postgresql 7.4.2 not using index
In-Reply-To: <1145980463.23538.218.camel@state.g2switchworks.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox