From: Arnau <arnaulist@andromeiberica.com>
To: pgsql-performance@postgresql.org
Subject: Re: Query on postgresql 7.4.2 not using index
Date: Tue, 25 Apr 2006 18:33:33 +0200
Message-ID: <444E4F5D.2070808@andromeiberica.com> (raw)
In-Reply-To: <1145980463.23538.218.camel@state.g2switchworks.com>
References: <444E240D.3050000@andromeiberica.com>
<3c1395330604250634r6f215ceame157f3dd5f5f4fa3@mail.gmail.com>
<444E28ED.3090900@andromeiberica.com>
<19483.1145978579@sss.pgh.pa.us>
<444E44A2.6030703@andromeiberica.com>
<1145980463.23538.218.camel@state.g2switchworks.com>
>>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
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: arnaulist@andromeiberica.com
Subject: Re: Query on postgresql 7.4.2 not using index
In-Reply-To: <444E4F5D.2070808@andromeiberica.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