indexes not being used! - Mailing list pgsql-admin
| From | Jodi Kanter |
|---|---|
| Subject | indexes not being used! |
| Date | |
| Msg-id | 004101c2efc0$65e6d0a0$de138f80@virginia.edu Whole thread Raw |
| Responses |
Re: indexes not being used!
|
| List | 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
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:
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)
-> 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:
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)
-> 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:
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)
-> 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)
-> 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:
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:
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 by date: