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 17:47:46 +0200
Message-ID: <444E44A2.6030703@andromeiberica.com> (raw)
In-Reply-To: <19483.1145978579@sss.pgh.pa.us>
References: <444E240D.3050000@andromeiberica.com>
<3c1395330604250634r6f215ceame157f3dd5f5f4fa3@mail.gmail.com>
<444E28ED.3090900@andromeiberica.com>
<19483.1145978579@sss.pgh.pa.us>
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
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: <444E44A2.6030703@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