Re: Query on postgresql 7.4.2 not using index

On Tue, 2006-04-25 at 08:49, Arnau wrote:
> chris smith wrote:
> > On 4/25/06, Arnau <arnaulist(at)andromeiberica(dot)com> wrote:
> >
> >>Hi all,
> >>
> >> I have the following running on postgresql version 7.4.2:
> >>
> >>CREATE SEQUENCE agenda_user_group_id_seq
> >>MINVALUE 1
> >>MAXVALUE 9223372036854775807
> >>CYCLE
> >>INCREMENT 1
> >>START 1;
> >>
> >>CREATE TABLE AGENDA_USERS_GROUPS
> >>(
> >> AGENDA_USER_GROUP_ID INT8
> >> CONSTRAINT pk_agndusrgrp_usergroup PRIMARY KEY
> >> DEFAULT NEXTVAL('agenda_user_group_id_seq'),
> >> USER_ID NUMERIC(10)
> >> CONSTRAINT fk_agenda_uid REFERENCES
> >>AGENDA_USERS (USER_ID)
> >> ON DELETE CASCADE
> >> NOT NULL,
> >> GROUP_ID NUMERIC(10)
> >> CONSTRAINT fk_agenda_gid REFERENCES
> >>AGENDA_GROUPS (GROUP_ID)
> >> ON DELETE CASCADE
> >> NOT NULL,
> >> CREATION_DATE DATE
> >> DEFAULT CURRENT_DATE,
> >> CONSTRAINT un_agndusrgrp_usergroup
> >>UNIQUE(USER_ID, GROUP_ID)
> >>);
> >>
> >>CREATE INDEX i_agnusrsgrs_userid ON AGENDA_USERS_GROUPS ( USER_ID );
> >>CREATE INDEX i_agnusrsgrs_groupid ON AGENDA_USERS_GROUPS ( GROUP_ID );

SNIP

> The same, the table has 2547556 entries:
>
> espsm_moviltelevision=# EXPLAIN ANALYZE SELECT agenda_user_group_id FROM
> agenda_users_groups
> espsm_moviltelevision-# WHERE group_id::int8 = 9;
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------
> Seq Scan on agenda_users_groups (cost=0.00..59477.34 rows=12738
> width=8) (actual time=3409.541..11818.794 rows=367026 loops=1)
> Filter: ((group_id)::bigint = 9)
> Total runtime: 13452.114 ms
> (3 filas)
>
> espsm_moviltelevision=# EXPLAIN ANALYZE SELECT agenda_user_group_id FROM
> agenda_users_groups
> espsm_moviltelevision-# WHERE group_id = '9';
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------
> 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

OK, a few points.

1: 7.4.2 is WAY out of date for the 7.4 series. The 7.4 series, also
it a bit out of date, and many issues in terms of performance have been
enhanced in the 8.x series. You absolutely should update to the latest
7.4 series, as there are known data loss bugs and other issues in the
7.4.2 version.

2: An index scan isn't always faster. In this instance, it looks like
the number of rows that match in the last version of your query is well
over 10% of the rows. Assuming your average row takes up <10% or so of
a block, which is pretty common, then you're going to have to hit almost
every block anyway to get your data. So, an index scan is no win.

3: To test whether or not an index scan IS a win, you can use the
enable_xxx settings to prove it to yourself:

set enable_seqscan = off;
explain analyze <your query here>;

and compare. Note that the enable_seqscan = off thing is a sledge
hammer, not a nudge, and generally should NOT be used in production. If
an index scan is generally a win for you, but the database isn't using
it, you might need to tune the database for your machine. note that you
should NOT tune your database based on a single query. You'll need to
reach a compromise on your settings that makes all your queries run
reasonably fast without the planner making insane decisions. One of the
better postgresql tuning docs out there is the one at:
http://www.varlena.com/GeneralBits/Tidbits/perf.html .

Good luck.