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 15:47:46
Message-ID: 444E44A2.6030703@andromeiberica.com (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane wrote:
> Arnau <arnaulist(at)andromeiberica(dot)com> writes:
> 
> 
>>  Seq Scan on agenda_users_groups  (cost=0.00..53108.45 rows=339675 
>>width=8) (actual time=916.903..5763.830 rows=367026 loops=1)
>>    Filter: (group_id = 9::numeric)
>>  Total runtime: 7259.861 ms
>>(3 filas)
> 
> 
>>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)

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)

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=407.193..2182.880 rows=367026 loops=1)
    Filter: ((group_id)::bigint = 9::bigint)
  Total runtime: 2506.998 ms
(3 rows)

espsm_moviltelevision=# select count(*) from agenda_users_groups ;
   count
---------
  2555437
(1 row)


   Postgresql then uses the index, I don't understand why? in this 
server I tried to tune the configuration, it's because of the tuning? 
Because it's a newer version of postgresql?


Thanks for all the replies
-- 
Arnau

In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2006-04-25 15:54:23
Subject: Re: Query on postgresql 7.4.2 not using index
Previous:From: Tom LaneDate: 2006-04-25 15:22:59
Subject: Re: Query on postgresql 7.4.2 not using index

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