Re: indexes not being used! - Mailing list pgsql-admin

I apologize if this is the wrong list. I have posted explain analyzes below.
If you have time and can assist I would appreciate it.
I will look into the other lists that you mentioned.
Thanks
Jodi

explain analyze select am_pk, smp_fk, am_comments, hybridization_name  from
arraymeasurement, groupref, grouplink where
(groupref.ref_fk=arraymeasurement.am_pk and  ((groupref.us_fk=1 and
groupref.us_fk=grouplink.us_fk and grouplink.gs_fk=groupref.gs_fk and
(groupref.permissions&128)>0) or (groupref.gs_fk=grouplink.gs_fk and
grouplink.us_fk=1 and (groupref.permissions&16)>0 )));
NOTICE:  QUERY PLAN:

Nested Loop  (cost=5.88..350.58 rows=1 width=55) (actual time=2.37..73.58
rows=43 loops=1)
  ->  Hash Join  (cost=5.88..47.57 rows=42 width=47) (actual
time=2.28..12.12 rows=43 loops=1)
        ->  Seq Scan on groupref  (cost=0.00..38.83 rows=313 width=16)
(actual time=0.05..8.32 rows=275 loops=1)
        ->  Hash  (cost=5.50..5.50 rows=150 width=31) (actual
time=2.06..2.06 rows=0 loops=1)
              ->  Seq Scan on arraymeasurement  (cost=0.00..5.50 rows=150
width=31) (actual time=0.04..1.45 rows=150 loops=1)
  ->  Seq Scan on grouplink  (cost=0.00..1.78 rows=78 width=8) (actual
time=0.01..0.44 rows=78 loops=43)
Total runtime: 74.00 msec



explain analyze select ref_fk from groupref, grouplink where
((groupref.us_fk=1 and groupref.us_fk=grouplink.us_fk and
grouplink.gs_fk=groupref.gs_fk and (groupref.permissions&128)>0) or
(groupref.gs_fk=grouplink.gs_fk and grouplink.us_fk=1 and
(groupref.permissions&16)>0 ));
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..2303.34 rows=1 width=24) (actual time=0.15..340.53
rows=272 loops=1)
  ->  Seq Scan on groupref  (cost=0.00..38.83 rows=313 width=16) (actual
time=0.07..9.12 rows=275 loops=1)
  ->  Seq Scan on grouplink  (cost=0.00..1.78 rows=78 width=8) (actual
time=0.01..0.42 rows=78 loops=275)
Total runtime: 341.30 msec


explain analyze select ref_fk from groupref, grouplink where
(groupref.us_fk=1 and groupref.us_fk=grouplink.us_fk and
grouplink.gs_fk=groupref.gs_fk and (groupref.permissions&128)>0);
NOTICE:  QUERY PLAN:

Merge Join  (cost=34.96..35.86 rows=32 width=20) (actual time=8.28..12.60
rows=252 loops=1)
  ->  Sort  (cost=30.72..30.72 rows=95 width=12) (actual time=7.17..7.59
rows=252 loops=1)
        ->  Seq Scan on groupref  (cost=0.00..27.62 rows=95 width=12)
(actual time=0.05..4.87 rows=252 loops=1)
  ->  Sort  (cost=4.23..4.23 rows=78 width=8) (actual time=1.08..1.56
rows=299 loops=1)
        ->  Seq Scan on grouplink  (cost=0.00..1.78 rows=78 width=8) (actual
time=0.02..0.46 rows=78 loops=1)
Total runtime: 13.30 msec



----- Original Message -----
From: "Joe Conway" <mail@joeconway.com>
To: "Jodi Kanter" <jkanter@virginia.edu>
Cc: "Postgres Admin List" <pgsql-admin@postgresql.org>
Sent: Friday, March 21, 2003 10:44 AM
Subject: Re: [ADMIN] indexes not being used!


> Jodi Kanter wrote:
> > We have a query that is causing performance problems. The indexes do
> > not appear to be used despite the fact that they exist. I dropped the
> > table and recreated from scratch. I reindexed as well and still no
> > luck. We vacuum analyze the system often. I will do my best to list
> > all that is happening. Any assistance would be greatly appreciated.
> > If you can offer some insight as to what the explains are telling me
> > that would be so helpful. Is there some documentation somewhere that
> > discusses such results? Thanks a lot! Jodi Kanter
>
> It's hard to tell from what you did post, but if the query returns a
> significant portion of the table then a seq scan is faster, and is
> properly picked by the optimizer. Please post EXPLAIN ANALYZE results.
>
> (and actually, this thread probably should be on the SQL or the PERFORM
> lists, not this one)
>
> Joe
>
>
>