Skip site navigation (1) Skip section navigation (2)

Re: Query on postgresql 7.4.2 not using index

From: Arnau <arnaulist(at)andromeiberica(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query on postgresql 7.4.2 not using index
Date: 2006-04-25 16:33:33
Message-ID: 444E4F5D.2070808@andromeiberica.com (view raw or flat)
Thread:
Lists: pgsql-performance
>>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

In response to

pgsql-performance by date

Next:From: andremachadoDate: 2006-04-25 16:38:16
Subject: Firebird 1.5.3 X Postgresql 8.1.3 (linux and windows)
Previous:From: Tom LaneDate: 2006-04-25 15:55:36
Subject: Re: Query on postgresql 7.4.2 not using index

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group