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
