Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I ran a query:

select * from orderline where orderid = 12345 and status > 0

that gave me the following plan:

Bitmap Heap Scan on orderline  (cost=286370.00..531410.78 rows=76372 width=155)
  Recheck Cond: ((orderid = 12081976) AND (itemstatus > 0))
  ->  BitmapAnd  (cost=286370.00..286370.00 rows=76372 width=0)
        ->  Bitmap Index Scan on orderline_orderid_index  (cost=0.00..4239.65 rows=229117 width=0)
              Index Cond: (orderid = 12081976)
        ->  Bitmap Index Scan on orderline_status  (cost=0.00..282091.91 rows=15274484 width=0)
              Index Cond: (status > 0)

I wanted to understand why postgres decided to run two separate index scans rather than the filter on the forign key (orderline_orderid - which returns about 5 rows out of millions) then filtering status from these results.

How can I work out what I've set up wrong here? (I'm assuming index fragmentation or maybe status shouldn't have an index?)

EDIT: I tried some of the things in the comments and the discovered the following:

I thought the cause of the issue was the index as I removed it and the problem went away:

Bitmap Heap Scan on orderline  (cost=4258.75..597209.93 rows=76372 width=155)
  Recheck Cond: (orderid = 12081976)
  Filter: (status > 0)
  ->  Bitmap Index Scan on orderline_orderid_index  (cost=0.00..4239.65 rows=229117 width=0)
        Index Cond: (orderid = 12081976)

However, I removed and added then index and then ran analyse and the the follow occured:

Index Scan using orderline_orderid_index on orderline  (cost=0.00..14.87 rows=4 width=63)
  Index Cond: (orderid = 12085242)
  Filter: (status > 0)

So is this because I rebuilt the index, or because I ran analyse?

share|improve this question

migrated from stackoverflow.com Feb 18 at 16:56

This question came from our site for professional and enthusiast programmers.

    
Do you have a very large number of rows and a very low number of different status values? –  DaveHowes Feb 18 at 13:28
    
Yes @DaveHowes, and how do I migrate? –  Mr Shoubs Feb 18 at 14:04
1  
I don;t know much about Postgres, but I have used Ingres in the past. It certainly didn't like indexes that had a small range of values - I'd try dropping that index and seeing what that does to the query plan –  DaveHowes Feb 18 at 14:37
    
You can click on close, chose off topic and you should be able to choose the dba site to migrate it to. See here for more on closing questions –  GarethD Feb 18 at 14:51
1  
What is default_statistics_target? What is the output of "select * from pg_stats where tablename ='orderline' and attname='orderid' \x\g\x" ? –  jjanes Feb 18 at 16:08

1 Answer 1

up vote 1 down vote accepted

It looks like Postgres had no idea how your values are distributed. It guessed that there's 229117 rows with orderid = 12081976 when it really was 5 of them. It has then chosen a plan which was optimal for 229117 rows.

When you ran analyze it was able to generate and save statistics about distribution of values in your table. And then it knew that there's about 4 rows with this orderid. And this allowed it to pick a better plan.

So it is analyze that caused this. Remember to run it after large changes to database data — for example after importing from external source.

Also — when you analyze your plans try to use explain analyze [query]; instead of just explain [query];. You can paste it to explain.depesz.com for easier to understand format.

share|improve this answer
    
I assumed vhcid in first plan is really orderid and itemstatus=status. It did not made sense to me otherwise. –  Tometzky Feb 18 at 17:16
    
yes, I changed it so the context would be more understandable... caught red handed :) –  Mr Shoubs Feb 18 at 17:19
    
this was on a restored db, so maybe that is what the stats didn't seem to exist. –  Mr Shoubs Feb 18 at 17:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.