I have a simple query that should return data about booking, but it misbehaves and sometimes does not return data within 1 minute.
So I have main booking table:
table booking_data - bid bigint primary key - user_id varchar(32) (other columns not relevant here and there is index on user_id)
and table with helper data, like if there are multiple flights on a booking, I wanna store the last flight datetime to helper table:
table itinerary_timestamps - bid references booking_data.bid - last_segment_arrival_at timestamp with time zone not null (some other columns and index on last_segment_arrival_at)
This is simplified query:
explain analyze select * from booking_data join itinerary_timestamps using (bid) where booking_data.user_id = 'dUZYLebTiZOBG1R3crKLhh' and itinerary_timestamps.last_segment_arrival_at < now() order by itinerary_timestamps.last_segment_arrival_at desc limit 11;
Limit (cost=1.00..1253.62 rows=11 width=515) (actual time=7171.379..10008.565 rows=11 loops=1) -> Nested Loop (cost=1.00..17263562.69 rows=151602 width=515) (actual time=7171.378..10008.551 rows=11 loops=1) -> Index Scan Backward using idx_itinerary_timestamps_last_segment_arrival_at on itinerary_timestamps (cost=0.44..781981.48 rows=25695264 width=60) (actual time=0.014..1918.143 rows=1760471 loops=1) Index Cond: (last_segment_arrival_at < now()) -> Index Scan using booking_data_pkey on booking_data (cost=0.56..0.64 rows=1 width=463) (actual time=0.004..0.004 rows=0 loops=1760471) Index Cond: (bid = itinerary_timestamps.bid) Filter: ((user_id)::text = 'dUZYLebTiZOBG1R3crKLhh'::text) Rows Removed by Filter: 1 Planning Time: 0.423 ms Execution Time: 10008.630 ms
There are 165867 bookings in booking_data for that user. There are 165862 records in itinerary_timestamps table with bid belonging to user_id and 26424052 total records.
Do you have any ideas how to optimize this simple query?
I was thinking of using bid for filtering that table, but it had similar results (sometimes better sometimes same):
explain analyze select * from booking_data join itinerary_timestamps using (bid) where booking_data.user_id = 'aQIDkXXEx3nWY5KvLSuEiK' and itinerary_timestamps.last_segment_arrival_at < now() and booking_data.bid in (select bid from booking_data where user_id='aQIDkXXEx3nWY5KvLSuEiK') order by itinerary_timestamps.last_segment_arrival_at desc limit 11;