I have table with two indexes:

  • latest_channel_snapshots_views_idx (view_count DESC NULLS LAST)
  • latest_channel_snapshots_network_views_idx (network_id, view_count DESC NULLS LAST)

What I want is to be able to sort by view_count overall, and within specific network. Postgres uses correct index for both of these cases. However, if i want to find record with most views where network_id is NULL, it uses the first index, and filters out the , thus performing really slow:

explain analyze SELECT *
FROM latest_channel_snapshots
WHERE network_id IS NULL
LIMIT 5 OFFSET 500000;

                                                                                     QUERY PLAN                                                                                      
 Limit  (cost=39599.04..39599.44 rows=5 width=74) (actual time=1926.271..1926.287 rows=5 loops=1)
   ->  Index Scan using latest_channel_snapshots_views_idx on latest_channel_snapshots  (cost=0.00..42001.59 rows=530336 width=74) (actual time=0.060..1899.224 rows=500005 loops=1)
         Filter: (network_id IS NULL)
         Rows Removed by Filter: 305022
 Total runtime: 1926.309 ms
(5 rows)
  • Which index is which -- can you name them please? Commented Sep 30, 2013 at 10:32
  • Why are you are using OFFSET 500000? Commented Sep 30, 2013 at 10:33
  • I strongly suspect the OFFSET 500000 is causing your slow performance. PG knows that it has to scan through 500005 rows and thus wants to use an index to scan rather than scan the underlying table as that causes less IO. Commented Sep 30, 2013 at 10:38
  • So, functionally, why do you need such a large offset? Commented Sep 30, 2013 at 10:39
  • I'll be paginating through these records via offset. 500,000 is just to demonstrate what slows it down. In reality, it will be up to few thousands.. Commented Sep 30, 2013 at 10:45

1 Answer 1


You have two issues here:

  1. large offset: PG will need to scan through the provided offset number of rows and will prefer an index to do this above a table scan.

  2. how to index the data: I think a partial index might help you here. Can you try

    create index xxx_idx on latest_channel_snapshots(view_count DESC NULLS LAST) where network_id is null;

    See http://www.postgresql.org/docs/9.2/static/indexes-partial.html for more information on partial indexes. In particular, example 11-2 on unbilled orders seems to apply to your case.

  • Thanks! Partial index solves the problem. I thought this wasn't necessary, but I was wrong obviously. Commented Sep 30, 2013 at 11:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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