indexes not being used!

Поиск
Список
Период
Сортировка
От Jodi Kanter
Тема indexes not being used!
Дата
Msg-id 004101c2efc0$65e6d0a0$de138f80@virginia.edu
обсуждение исходный текст
Ответы Re: indexes not being used!  (Joe Conway <mail@joeconway.com>)
Список pgsql-admin
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
 
This is the table:
genex=# \d groupref
         Table "groupref"
   Column    |  Type   | Modifiers
-------------+---------+-----------
 us_fk       | integer | not null
 gs_fk       | integer | not null
 ref_fk      | integer | not null
 permissions | integer |
Indexes: groupref_gs_fk_ind,
         groupref_permissions,
         groupref_us_fk_ind
Unique keys: groupref_ref_fk_ind
 
This is the original query and it's explain results:
genex=# explain 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)
  ->  Hash Join  (cost=5.88..47.57 rows=42 width=47)
        ->  Seq Scan on groupref  (cost=0.00..38.83 rows=313 width=16)
        ->  Hash  (cost=5.50..5.50 rows=150 width=31)
              ->  Seq Scan on arraymeasurement  (cost=0.00..5.50 rows=150 width=31)
  ->  Seq Scan on grouplink  (cost=0.00..1.78 rows=78 width=8)
 
Why is it ignoring indexes? There is a double join between groupref and grouplink. I thought this could be the trouble but you'll se below that I started to simplify the query below and still have trouble.
 
Next I eliminated a join to the main data table and got this:
genex=# explain 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)
  ->  Seq Scan on groupref  (cost=0.00..38.83 rows=313 width=16)
  ->  Seq Scan on grouplink  (cost=0.00..1.78 rows=78 width=8)
 
I thought that the OR part of the statement might be causing trouble so I eliminated it and got:
genex=# explain 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:
 
Hash Join  (cost=18.71..20.89 rows=1 width=20)
  ->  Seq Scan on grouplink  (cost=0.00..1.78 rows=78 width=8)
  ->  Hash  (cost=18.71..18.71 rows=2 width=12)
        ->  Index Scan using groupref_us_fk_ind on groupref  (cost=0.00..18.71 rows=2 width=12)
Now an index is being used??
 
I did a vaccum analyze again and ran the same query. The explain results changed to this:
Merge Join  (cost=34.96..35.86 rows=32 width=20)
  ->  Sort  (cost=30.72..30.72 rows=95 width=12)
        ->  Seq Scan on groupref  (cost=0.00..27.62 rows=95 width=12)
  ->  Sort  (cost=4.23..4.23 rows=78 width=8)
        ->  Seq Scan on grouplink  (cost=0.00..1.78 rows=78 width=8)
 
I backed off to a very simple query and still cannot get the indexes working.
genex=# explain select ref_fk from groupref where (groupref.us_fk=1 and (groupref.permissions&128)>0);
NOTICE:  QUERY PLAN:
 
Seq Scan on groupref  (cost=0.00..27.62 rows=95 width=4)
 
EXPLAIN
genex=# explain select ref_fk from groupref where (groupref.us_fk=1);
NOTICE:  QUERY PLAN:
 
Seq Scan on groupref  (cost=0.00..22.01 rows=284 width=4)

_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu


 

 

 

В списке pgsql-admin по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: psql invokation
Следующее
От: Joe Conway
Дата:
Сообщение: Re: indexes not being used!