Check out the explain analyze output here:

http://explain.depesz.com/s/qBY

There's a line:

Index Scan using c_id_pkey on c (cost=0.00..3.73 rows=1 width=683) (actual time=4.114..4.115 rows=0 loops=32058)
Index Cond: (id = a.c_id)
Filter: (pulldate >= '2013-07-11 05:00:00+00'::timestamp with time zone)

that takes 131918.670 ms. I assume the main problem is the 32,058 loops. What steps can I take to reduce the number of loops here? It's already using the index scan, and I also have an index for pulldate on the "c" table as well.

Original query:

SELECT *
FROM "a" INNER JOIN "b" ON ("a"."b_id" = "b"."id") 
INNER JOIN "e" ON ("b"."e_id" = "e"."id") 
INNER JOIN "c" ON ("a"."c_id" = "c"."id") 
INNER JOIN "d" ON ("b"."d_id" = "d"."id") 
INNER JOIN "d" T6 ON ("c"."d_id" = T6."id") 
WHERE ("a"."state" < 2  
AND "b"."count" = true  
AND "b"."e_id" = 2  
AND "c"."pulldate" >= '2013-07-11 00:00:00-05:00' ) 
ORDER BY "c"."pulldate" DESC, "c"."pubdate" DESC LIMIT 10

And original explain analyze output (before placing in the link above):

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=112346.80..112346.82 rows=10 width=1306) (actual time=136663.244..136663.277 rows=10 loops=1)
   ->  Sort  (cost=112346.80..112371.07 rows=9710 width=1306) (actual time=136663.239..136663.251 rows=10 loops=1)
         Sort Key: c.pulldate, c.pubdate
         Sort Method: top-N heapsort  Memory: 48kB
         ->  Hash Join  (cost=2266.87..112136.97 rows=9710 width=1306) (actual time=6537.254..136624.192 rows=4266 loops=1)
               Hash Cond: (c.d_id = t6.id)
               ->  Nested Loop  (cost=1013.39..110665.02 rows=9710 width=1100) (actual time=6308.487..136363.896 rows=4266 loops=1)
                     ->  Hash Join  (cost=1013.39..24376.61 rows=23051 width=417) (actual time=260.599..4269.521 rows=32058 loops=1)
                           Hash Cond: (a.b_id = b.id)
                           ->  Seq Scan on a  (cost=0.00..19481.79 rows=973581 width=17) (actual time=0.010..2335.691 rows=999110 loops=1)
                                 Filter: (state < 2)
                           ->  Hash  (cost=1011.54..1011.54 rows=148 width=400) (actual time=260.525..260.525 rows=148 loops=1)
                                 Buckets: 1024  Batches: 1  Memory Usage: 64kB
                                 ->  Nested Loop  (cost=5.40..1011.54 rows=148 width=400) (actual time=1.466..259.519 rows=148 loops=1)
                                       ->  Nested Loop  (cost=5.40..65.45 rows=148 width=194) (actual time=1.392..56.017 rows=148 loops=1)
                                             ->  Seq Scan on e  (cost=0.00..2.73 rows=1 width=156) (actual time=0.036..0.070 rows=1 loops=1)
                                                   Filter: (id = 2)
                                             ->  Bitmap Heap Scan on b  (cost=5.40..61.25 rows=148 width=38) (actual time=1.336..55.424 rows=148 loops=1)
                                                   Recheck Cond: (e_id = 2)
                                                   Filter: count
                                                   ->  Bitmap Index Scan on b_e_id  (cost=0.00..5.36 rows=148 width=0) (actual time=1.312..1.312 rows=148 loops=1)
                                                         Index Cond: (e_id = 2)
                                       ->  Index Scan using d_id_pkey on d  (cost=0.00..6.38 rows=1 width=206) (actual time=1.362..1.366 rows=1 loops=148)
                                             Index Cond: (id = b.d_id)
                     ->  Index Scan using c_id_pkey on c  (cost=0.00..3.73 rows=1 width=683) (actual time=4.114..4.115 rows=0 loops=32058)
                           Index Cond: (id = a.c_id)
                           Filter: (pulldate >= '2013-07-11 05:00:00+00'::timestamp with time zone)
               ->  Hash  (cost=1145.99..1145.99 rows=8599 width=206) (actual time=228.726..228.726 rows=5532 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 1324kB
                     ->  Seq Scan on d t6  (cost=0.00..1145.99 rows=8599 width=206) (actual time=0.015..213.686 rows=5532 loops=1)
 Total runtime: 136664.907 ms
(31 rows)

My previous post for this database is here:
viewtopic.php?f=20&t=10129&p=58437

Let me know if I need to post more info. Any help is appreciated!

Hi,
is the issue resolved? If not, please try removing the order by condition and see if the time drops to half the current or thereabout.
How many records are there in c and how many really get selected on applying the filter on pulldate?
Did you do an explain or explain analyze?