PostgreSQL: ... LIMIT 1 much slower than fetching all rows?

Question

If I fetch only one row, the query takes much longer: 1433 ms vs 23 ms

Is there a work around?

Slow:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM "modwork_ticket" WHERE 
 "modwork_ticket"."email_sender_id" = '[email protected]' 
 ORDER BY "modwork_ticket"."date_created" ASC limit 1;

--------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..542.14 rows=1 width=1146) (actual time=1433.128..1433.129 rows=1 loops=1)
   Buffers: shared hit=1466151 read=4661
   ->  Index Scan using modwork_ticket_date_created on modwork_ticket  (cost=0.43..606714.36 rows=1120 width=1146) (actual time=1433.125..1433
         Filter: ((email_sender_id)::text = '[email protected]'::text)
         Rows Removed by Filter: 1705251
         Buffers: shared hit=1466151 read=4661
 Planning time: 2.504 ms
 Execution time: 1433.218 ms

Fast:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM "modwork_ticket" WHERE
  "modwork_ticket"."email_sender_id" = '[email protected]' 
  ORDER BY "modwork_ticket"."date_created" ASC;


--------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=4335.83..4338.63 rows=1120 width=1146) (actual time=23.637..23.794 rows=584 loops=1)
   Sort Key: date_created
   Sort Method: quicksort  Memory: 732kB
   Buffers: shared hit=544 read=4
   ->  Bitmap Heap Scan on modwork_ticket  (cost=25.11..4279.11 rows=1120 width=1146) (actual time=20.479..22.595 rows=584 loops=1)
         Recheck Cond: ((email_sender_id)::text = '[email protected]'::text)
         Heap Blocks: exact=538
         Buffers: shared hit=541 read=4
         ->  Bitmap Index Scan on modwork_ticket_email_sender_id_like  (cost=0.00..24.83 rows=1120 width=0) (actual time=20.388..20.388 rows=
               Index Cond: ((email_sender_id)::text = '[email protected]'::text)
               Buffers: shared hit=3 read=4
 Planning time: 0.173 ms
 Execution time: 23.974 ms

Version:

PostgreSQL 9.6.3 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 4.7.2 20130108 [gcc-4_7-branch revision 195012], 64-bit

I run "VACUUM ANALYZE" before running above queries.

Answer 1

The problem is that rows for '[email protected]' are deficient in values with an early date_created, but the planner doesn't know that. It thinks they are evenly scattered, so it will find one early on when walking modwork_ticket_date_created and will be able to stop then. But instead it has to walk through 1,705,251 of them.

You can create an index on (email_sender_id text_pattern_ops, date_created) and it will be able to jump directly to the tuple you want. That should be much faster than either existing plan. (I proposed the including text_pattern_ops purely because the name of the existing index has the word "like" in it, so I assume it was already defined that way--but maybe you actually have a column named "like". It would be better if you show us the definitions so we don't have to guess about things such as this). This index may be able to replace an existing one, so the overall number of indexes might stay the same.

If you don't want to create the index and just want it to the use the 2nd plan, you can force it by writing the query like this:

SELECT * FROM modwork_ticket WHERE 
 modwork_ticket.email_sender_id = '[email protected]' 
 ORDER BY modwork_ticket.date_created + '0 days' ASC limit 1;

By adding something to the date before you sort, it tricks PostgreSQL into thinking it can't use the date index.

Once v10 of PostgreSQL is released and you upgrade to it, you can create an advanced statistics definition for the functional dependency between the two columns, which might also be enough to get it to choose the better plan.

Answer 2

• In the fast one the query scans the modwork_ticket_email_sender_id_like index
• In the other it scans the modwork_ticket_date_created index

So it thinks there is less work to start from the oldest row on the ordered index until it finds a row that has ((email_sender_id)::text = '[email protected]'::text) than to collect all ((email_sender_id)::text = '[email protected]'::text) on the index and then sort them to find the oldest.

You may want to try to update the statistics,

ANALYZE modwork_ticket;

And then try rerunning the query again. If that doesn't work, you may want to increase the statistics on (email_sender_id)::text. And if that doesn't work you may want to paste both of your indexes so we can advise you -- or consider merging them (email_sender_id, date_created).