69 rows and 12 rows filtered on applying functional index and actualy query to the PG exercises exercise here:

12_rows_after_filtering.md

The 12 Tennis Court Bookings (Filtered Results)

Time	Facility	Member ID	Slots
08:00:00	Tennis Court 1	26	3
08:00:00	Tennis Court 2	12	3
09:30:00	Tennis Court 1	11	3
10:00:00	Tennis Court 2	16	3
11:30:00	Tennis Court 2	1	3
12:00:00	Tennis Court 1	22	3
13:30:00	Tennis Court 1	16	3
14:00:00	Tennis Court 2	9	3
15:30:00	Tennis Court 1	5	3
16:00:00	Tennis Court 2	10	3
17:00:00	Tennis Court 1	17	6
18:00:00	Tennis Court 2	27	3

69_rows_before filtering.md

Here are all 69 bookings from September 21, 2012 in a simple markdown table:

All 69 Bookings on September 21, 2012

Time	Facility	Member ID	Slots
08:00:00	Tennis Court 1	26	3
08:00:00	Tennis Court 2	12	3
08:30:00	Pool Table	33	2
08:30:00	Snooker Table	21	2
09:00:00	Badminton Court	9	3
09:00:00	Table Tennis	29	2
09:30:00	Tennis Court 1	11	3
09:30:00	Pool Table	21	2
09:30:00	Snooker Table	5	2
09:30:00	Squash Court	0	2
09:30:00	Massage Room 1	16	2
10:00:00	Tennis Court 2	16	3
10:00:00	Table Tennis	30	2
10:30:00	Badminton Court	21	3
10:30:00	Pool Table	28	1
10:30:00	Squash Court	13	2
10:30:00	Massage Room 1	14	2
11:00:00	Table Tennis	2	2
11:00:00	Pool Table	3	1
11:30:00	Tennis Court 2	1	3
11:30:00	Massage Room 1	0	2
11:30:00	Snooker Table	10	2
11:30:00	Squash Court	0	4
12:00:00	Tennis Court 1	22	3
12:00:00	Badminton Court	9	3
12:00:00	Massage Room 2	15	2
12:30:00	Pool Table	29	1
13:00:00	Table Tennis	20	2
13:00:00	Pool Table	12	1
13:00:00	Snooker Table	24	2
13:00:00	Massage Room 1	0	2
13:30:00	Tennis Court 1	16	3
14:00:00	Tennis Court 2	9	3
14:00:00	Badminton Court	0	6
14:00:00	Table Tennis	21	2
14:00:00	Pool Table	28	1
14:00:00	Squash Court	0	2
14:30:00	Massage Room 1	14	2
14:30:00	Pool Table	29	1
14:30:00	Snooker Table	4	2
15:00:00	Pool Table	6	1
15:30:00	Tennis Court 1	5	3
15:30:00	Table Tennis	4	2
15:30:00	Massage Room 1	0	2
15:30:00	Squash Court	12	2
16:00:00	Tennis Court 2	10	3
16:00:00	Pool Table	3	1
16:00:00	Snooker Table	24	2
16:30:00	Table Tennis	30	2
16:30:00	Massage Room 1	9	2
16:30:00	Pool Table	15	1
17:00:00	Tennis Court 1	17	6
17:00:00	Pool Table	8	1
17:00:00	Snooker Table	13	2
17:30:00	Massage Room 1	1	2
17:30:00	Massage Room 2	0	2
17:30:00	Squash Court	12	2
18:00:00	Tennis Court 2	27	3
18:00:00	Pool Table	29	1
18:30:00	Table Tennis	29	2
18:30:00	Massage Room 1	3	2
18:30:00	Pool Table	33	1
18:30:00	Squash Court	14	4
19:00:00	Pool Table	30	1
19:00:00	Snooker Table	5	2
19:30:00	Table Tennis	1	2
19:30:00	Massage Room 1	20	2
19:30:00	Pool Table	3	1

actual_query_with_date.md

EXPLAIN ( ANALYZE, Buffers ) SELECT starttime AS start, name FROM cd.bookings JOIN cd.facilities ON cd.bookings.facid = cd.facilities.facid WHERE date(starttime) = '2012-09-21' AND name LIKE 'Tennis Court%' ORDER BY START ASC;

diablo_breakdown.md

https://explain.dalibo.com/plan/90ahac282hgdg0ee#stats https://explain.dalibo.com/plan/ch1e6g8c614cce84#stats

Actual blog: https://awellroundedengineer.substack.com/p/avoid-functions-on-indexed-column

query_plan_with_date.md

                                                                QUERY PLAN

---

Sort (cost=34.17..34.18 rows=4 width=22) (actual time=0.737..0.740 rows=12 loops=1) Sort Key: bookings.starttime Sort Method: quicksort Memory: 25kB Buffers: shared hit=5 -> Hash Join (cost=5.57..34.13 rows=4 width=22) (actual time=0.692..0.712 rows=12 loops=1) Hash Cond: (bookings.facid = facilities.facid) Buffers: shared hit=5 -> Bitmap Heap Scan on bookings (cost=4.44..32.92 rows=20 width=12) (actual time=0.653..0.662 rows=69 loops=1) Recheck Cond: (date(starttime) = '2012-09-21'::date) Heap Blocks: exact=2 Buffers: shared hit=4 -> Bitmap Index Scan on idx_bookings_date_starttime (cost=0.00..4.43 rows=20 width=0) (actual time=0.044..0.044 rows=69 loops=1) Index Cond: (date(starttime) = '2012-09-21'::date) Buffers: shared hit=2 -> Hash (cost=1.11..1.11 rows=2 width=18) (actual time=0.028..0.029 rows=2 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=1 -> Seq Scan on facilities (cost=0.00..1.11 rows=2 width=18) (actual time=0.009..0.010 rows=2 loops=1) Filter: ((name)::text ~~ 'Tennis Court%'::text) Rows Removed by Filter: 7 Buffers: shared hit=1 Planning: Buffers: shared hit=20 Planning Time: 1.713 ms Execution Time: 1.083 ms (25 rows)