Re: Query on postgresql 7.4.2 not using index - Mailing list pgsql-performance
From | Arnau |
---|---|
Subject | Re: Query on postgresql 7.4.2 not using index |
Date | |
Msg-id | 444E44A2.6030703@andromeiberica.com Whole thread Raw |
In response to | Re: Query on postgresql 7.4.2 not using index (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: Query on postgresql 7.4.2 not using index
(Scott Marlowe <smarlowe@g2switchworks.com>)
Re: Query on postgresql 7.4.2 not using index (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-performance |
Tom Lane wrote: > Arnau <arnaulist@andromeiberica.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
pgsql-performance by date: