Why would adding LIMIT 200 cause a query to slow down?

Question

I am trying to debug a slow query on a PostgreSQL 9.1.13 database, and I am a bit at loss. The exact query generated by the ORM framework is:

SELECT "core_product"."sales_price", "core_product"."recommended_price", "core_productgroup"."name", "core_product"."number", "core_product"."name", "core_product"."description", "core_product"."cost_price", "core_product"."bar_code", "core_product"."accessible"
FROM "core_product" INNER JOIN "core_productgroup" ON ( "core_product"."product_group_id" = "core_productgroup"."id" )
WHERE "core_productgroup"."company_id" = 1056
ORDER BY "core_product"."id" ASC
LIMIT 200;

This query takes 28 seconds to return 200 rows, which is too slow for our use case.

In a first attempt to understand where the performance bottleneck might be. I tried first removing the LIMIT 200 expecting it to be even slower. However without LIMIT 200 the query takes just 2 seconds to return approximately 293000 rows.

How can it be faster to return all 293000 matching rows rather than only the first 200?

I have tried using EXPLAIN to see how the query plans for the two queries differ. It turns out these two almost identical queries have quite different query plans. With LIMIT:

                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.69..52229.70 rows=200 width=76)
   ->  Nested Loop  (cost=10.69..17054740.55 rows=65320 width=76)
         Join Filter: (core_product.product_group_id = core_productgroup.id)
         ->  Index Scan using core_product_pkey on core_product  (cost=0.00..3124799.28 rows=2957497 width=71)
         ->  Materialize  (cost=10.69..131.18 rows=314 width=13)
               ->  Bitmap Heap Scan on core_productgroup  (cost=10.69..129.61 rows=314 width=13)
                     Recheck Cond: (company_id = 1056)
                     ->  Bitmap Index Scan on core_productgroup_company_id  (cost=0.00..10.61 rows=314 width=0)
                           Index Cond: (company_id = 1056)

Without LIMIT:

                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Sort  (cost=110561.36..110724.66 rows=65320 width=76)
   Sort Key: core_product.id
   ->  Hash Join  (cost=133.54..102432.32 rows=65320 width=76)
         Hash Cond: (core_product.product_group_id = core_productgroup.id)
         ->  Seq Scan on core_product  (cost=0.00..90554.97 rows=2957497 width=71)
         ->  Hash  (cost=129.61..129.61 rows=314 width=13)
               ->  Bitmap Heap Scan on core_productgroup  (cost=10.69..129.61 rows=314 width=13)
                     Recheck Cond: (company_id = 1056)
                     ->  Bitmap Index Scan on core_productgroup_company_id  (cost=0.00..10.61 rows=314 width=0)
                           Index Cond: (company_id = 1056)

Is there some way I can influence the query plan chosen by PostgreSQL to avoid the very inefficient query plan it is currently using when LIMIT is being used?

Verbose query plan with LIMIT:

                                                                                                                                                                                                                            QUERY PLAN                                                                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.69..52229.70 rows=200 width=76) (actual time=41669.575..41681.069 rows=200 loops=1)
   Output: core_product.sales_price, core_product.recommended_price, core_productgroup.name, core_product.number, core_product.name, core_product.description, core_product.cost_price, core_product.bar_code, core_product.accessible, core_product.id
   ->  Nested Loop  (cost=10.69..17054740.55 rows=65320 width=76) (actual time=41669.573..41681.040 rows=200 loops=1)
         Output: core_product.sales_price, core_product.recommended_price, core_productgroup.name, core_product.number, core_product.name, core_product.description, core_product.cost_price, core_product.bar_code, core_product.accessible, core_product.id
         Join Filter: (core_product.product_group_id = core_productgroup.id)
         ->  Index Scan using core_product_pkey on public.core_product  (cost=0.00..3124799.28 rows=2957497 width=71) (actual time=0.033..803.265 rows=773270 loops=1)
               Output: core_product.id, core_product.product_group_id, core_product.name, core_product.sales_price, core_product.cost_price, core_product.recommended_price, core_product.accessible, core_product.volume, core_product.in_stock, core_product.on_order, core_product.ordered, core_product.available, core_product.bar_code, core_product.description, core_product.logical_timestamp, core_product.number, core_product.unit, core_product.uuid
         ->  Materialize  (cost=10.69..131.18 rows=314 width=13) (actual time=0.000..0.018 rows=300 loops=773270)
               Output: core_productgroup.name, core_productgroup.id
               ->  Bitmap Heap Scan on public.core_productgroup  (cost=10.69..129.61 rows=314 width=13) (actual time=0.073..0.140 rows=300 loops=1)
                     Output: core_productgroup.name, core_productgroup.id
                     Recheck Cond: (core_productgroup.company_id = 1056)
                     ->  Bitmap Index Scan on core_productgroup_company_id  (cost=0.00..10.61 rows=314 width=0) (actual time=0.060..0.060 rows=300 loops=1)
                           Index Cond: (core_productgroup.company_id = 1056)
 Total runtime: 41681.125 ms
(15 rows)

Verbose query plan without LIMIT:

                                                                                                                                                                                                                            QUERY PLAN                                                                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=110561.36..110724.66 rows=65320 width=76) (actual time=1733.710..1831.820 rows=292797 loops=1)
   Output: core_product.sales_price, core_product.recommended_price, core_productgroup.name, core_product.number, core_product.name, core_product.description, core_product.cost_price, core_product.bar_code, core_product.accessible, core_product.id
   Sort Key: core_product.id
   Sort Method: external merge  Disk: 28688kB
   ->  Hash Join  (cost=133.54..102432.32 rows=65320 width=76) (actual time=1.561..1239.564 rows=292797 loops=1)
         Output: core_product.sales_price, core_product.recommended_price, core_productgroup.name, core_product.number, core_product.name, core_product.description, core_product.cost_price, core_product.bar_code, core_product.accessible, core_product.id
         Hash Cond: (core_product.product_group_id = core_productgroup.id)
         ->  Seq Scan on public.core_product  (cost=0.00..90554.97 rows=2957497 width=71) (actual time=0.006..726.778 rows=3051563 loops=1)
               Output: core_product.id, core_product.product_group_id, core_product.name, core_product.sales_price, core_product.cost_price, core_product.recommended_price, core_product.accessible, core_product.volume, core_product.in_stock, core_product.on_order, core_product.ordered, core_product.available, core_product.bar_code, core_product.description, core_product.logical_timestamp, core_product.number, core_product.unit, core_product.uuid
         ->  Hash  (cost=129.61..129.61 rows=314 width=13) (actual time=0.186..0.186 rows=300 loops=1)
               Output: core_productgroup.name, core_productgroup.id
               Buckets: 1024  Batches: 1  Memory Usage: 13kB
               ->  Bitmap Heap Scan on public.core_productgroup  (cost=10.69..129.61 rows=314 width=13) (actual time=0.055..0.111 rows=300 loops=1)
                     Output: core_productgroup.name, core_productgroup.id
                     Recheck Cond: (core_productgroup.company_id = 1056)
                     ->  Bitmap Index Scan on core_productgroup_company_id  (cost=0.00..10.61 rows=314 width=0) (actual time=0.045..0.045 rows=300 loops=1)
                           Index Cond: (core_productgroup.company_id = 1056)
 Total runtime: 1883.235 ms
(18 rows)

Answer 1

The planner thinks that it can run through in core_product.id order, and rapidly find 200 matches where company_id=1056, at which point it is done.

But that doesn't work out, because all the things with a small core_product.id are things which don't have company_id=1056. (For example, company_id=1056 is a recently-joined client of yours, so all of their data falls on the upper end of the id sequence. But PostgreSQL doesn't understand that.)

You can probably force the plan you want by using a CTE and writing it like this:

with t as (
   <your query, without the limit, goes here>
)
select * from t limit 200;

Answer 2

This link says you are not able to directly influence the join.

The query plan uses statistics on the table to choose its plan, so you might see better performance after you use ANALYZE on the tables