OLIST SQL - Techniques & Optimisation¶
This is the second half of the OLIST SQL project. The first notebook was more of a revision, where I walked back through the analysis I had already done. This one is a bit different, in that these are techniques and ideas I didn't have a real reason to use yet. So I gave each one an actual question from the OLIST data and tried it to see how it would behave.
The voice here is more exploratory than the first notebook. It is less on stating a finding and more on walking through something I wanted to understand, what happened when I tried it and where to read more.
import warnings
warnings.filterwarnings('ignore')
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker
from sqlalchemy import create_engine
from scipy.stats import kruskal
engine = create_engine('postgresql+psycopg2://postgres:postgres@localhost:5432/olist_db')
# Query fct:
def q(sql):
with engine.connect() as conn:
return pd.read_sql(sql, conn)
sns.set_theme(style = 'whitegrid', context = 'notebook')
plt.rcParams.update({
'figure.dpi': 130,
'font.family': 'sans-serif',
'axes.spines.top': False,
'axes.spines.right': False,
'axes.titlesize': 14,
'axes.titleweight': 'bold',
'axes.titlepad': 12,
'axes.labelsize': 11,
})
PRIMARY = '#1565C0'
ACCENT = '#E53935'
MUTED = '#90CAF9'
SPECIAL = "#0F9DB6"
print('Connected to olist_db')
q("select current_database() as db")
Connected to olist_db
| db | |
|---|---|
| 0 | olist_db |
11. Query optimisation with explain analyze¶
I had written a lot of queries by this point but never really stopped to ask why some of them were slow, or what postgres was doing under the hood when it ran them. This section is me starting to look at that. 4 explain analyze runs a query and prints the full execution plan. Which steps the planner chose, how long each one took and how many rows it looked at each stage. It's the main tool for diagnosing performance in postgres and the docs or this article are worth a read if you've never used it.
I picked three cases, each one a different kind of index. They're a missing foreign key index on order_reviews, an expression index for the date_trunc('month', ...) grouping that shows up in nearly every monthly revenue query from the first notebook, and a covering index on order_payments.
The useful part was working out why, and then finding the query that actually makes each one gets used. They turn out to get used for three different reasons. The foreign key index earns its keep on a single row selective lookup, the expression index on a query that wants one month rather than all of them, and the covering index on a query whose columns all live inside the index. In every case the plan is what tells you that (not the clock).
from sqlalchemy import text
def explain(sql, buffers=False):
flag = '(analyze, buffers)' if buffers else 'analyze'
with engine.connect() as conn:
rows = conn.execute(text(f'explain {flag} {sql}'))
for row in rows:
print(row[0])
# what indexes already exist?
q("""
select
t.relname as table_name,
i.relname as index_name,
array_to_string(array_agg(a.attname order by x.n), ', ') as columns
from pg_class t
join pg_index ix on ix.indrelid = t.oid
join pg_class i on i.oid = ix.indexrelid
join pg_attribute a on a.attrelid = t.oid
join generate_subscripts(ix.indkey, 1) as x(n)
on a.attnum = ix.indkey[x.n]
where t.relkind = 'r'
and t.relname in ('orders', 'order_items', 'order_payments',
'order_reviews', 'customers', 'sellers', 'products')
group by t.relname, i.relname
order by t.relname, i.relname
""")
| table_name | index_name | columns | |
|---|---|---|---|
| 0 | customers | customers_pkey | customer_id |
| 1 | customers | idx_customers_state | customer_state |
| 2 | order_items | idx_order_items_product | product_id |
| 3 | order_items | idx_order_items_seller | seller_id |
| 4 | order_items | order_items_pkey | order_id, order_item_id |
| 5 | order_payments | order_payments_pkey | order_id, payment_sequential |
| 6 | order_reviews | idx_reviews_score | review_score |
| 7 | orders | idx_orders_customer_id | customer_id |
| 8 | orders | idx_orders_status | order_status |
| 9 | orders | orders_pkey | order_id |
| 10 | products | idx_products_category | product_category_name |
| 11 | products | products_pkey | product_id |
| 12 | sellers | sellers_pkey | seller_id |
Since there is a lot of noice when running a query, a single explain analyze run isn't enough to claim one version of a query is faster than another. I'd already seen single timings swing 2-3x with zero code changes just from the cache and scheduling noise, so eyeballing one before/after pair and calling it a finding isn't honest.
The fix is to actually benchmark each comparison. We can try to warm the cache with a couple of throwaway runs so both sides start from the same state, then run each version n times and look at the median instead of a single draw. On top of that, we can also use a Mann-Whitney U test to check whether the before/after timings are statistically distinguishable at all, rather than just whichever one happened to land lower this time. Execution times are skewed with occasional contention spikes rather than normally distributed, which is why we use a rank based test.
Since the compute cost is almost negligable n=50 should be sufficient to get results (that are consistant). An inconclusive result is more trustworthy than a confident claim based on a single "lucky" run. Another interesting thing is the buffer count from explain (analyze, buffers). It gives a second way to check, since it stays the same across runs of the same plan. So even if the timing test doesn't show a clear difference, the buffer count can still confirm whether the index is actually reducing the work done.
import re
import numpy as np
from scipy.stats import mannwhitneyu
def run_once(sql, buffers=True):
flag = '(analyze, buffers)' if buffers else 'analyze'
with engine.connect() as conn:
rows = conn.execute(text(f'explain {flag} {sql}')).fetchall()
return '\n'.join(r[0] for r in rows)
def benchmark(sql, n=50, warmup=2, buffers=True):
for _ in range(warmup):
run_once(sql, buffers=buffers)
times, plan = [], None
for _ in range(n):
plan = run_once(sql, buffers=buffers)
times.append(float(re.search(r'Execution Time: ([\d.]+) ms', plan).group(1)))
buff = None
if buffers:
#! root node's Buffers line carries the cumulative total for the whole plan -
#! summing every "Buffers:" line in the output double-counts child nodes
m = re.search(r'Buffers: shared hit=(\d+)(?:\s+read=(\d+))?', plan)
buff = int(m.group(1)) + int(m.group(2) or 0)
return np.array(times), buff, plan
def compare(label, sql, setup_before, setup_after, n=50):
setup_before()
t_before, buff_before, plan_before = benchmark(sql, n=n)
setup_after()
t_after, buff_after, plan_after = benchmark(sql, n=n)
p = mannwhitneyu(t_before, t_after, alternative='two-sided').pvalue
print('=' * 100)
print(f'{label} - BEFORE (one representative run of {n})')
print('=' * 100)
print(plan_before)
print()
print('=' * 100)
print(f'{label} - AFTER (one representative run of {n})')
print('=' * 100)
print(plan_after)
print()
print(f'--- {n} runs each, after warm-up ---')
print(f'before: median={np.median(t_before):.2f}ms IQR=[{np.percentile(t_before, 25):.1f}, {np.percentile(t_before, 75):.1f}] buffers={buff_before}')
print(f'after : median={np.median(t_after):.2f}ms IQR=[{np.percentile(t_after, 25):.1f}, {np.percentile(t_after, 75):.1f}] buffers={buff_after}')
print(f'mann-whitney U p-value = {p:.4g}')
return t_before, t_after
Case study 1 - missing index on order_reviews(order_id)¶
Postgres enforces foreign key constraints but it doesn't automatically build an index on the foreign key column. The standard advice you can find is that without an index on order_reviews.order_id every join has to do a full sequential scan through all 99k rows. So expecting the obvious change to work, we droped the index, ran tha explain analyze, recreated it, ran it again, and then watched for the plan to change.
Unfortunately, it didn't change at all. The planner chose the same Hash Join both times, with the same Seq Scan on order_reviews. The created index never even showed up in the plan.
While we can see that the timing is faster on the second run (even when using the compare() function), it is easy to think that the index is doing its job. But it isnt, the two queries always run in the same order, the reason the second one runs faster is because of a "warm cache". If the index were actually helping it would show up as an index scan, and it never does.
When looking at it in a bit more detail the behaviour makes sense. At 99k rows order_reviews fits comfortably in postgres's shared buffer pool, and for a join that needs most of those rows anyway, building one hash table beats doing an index lookup per row. The planner already knew that, which is exactly why it ignored the index.
delivery_query = """
select
case
when dd <= 3 then '0-3 days'
when dd <= 7 then '4-7 days'
when dd <= 14 then '8-14 days'
when dd <= 21 then '15-21 days'
else '22+ days'
end as bucket,
count(*) as orders,
round(avg(rev.review_score), 2) as avg_score
from (
select
o.order_id,
extract(day from (
o.order_delivered_customer_date - o.order_purchase_timestamp
)) as dd
from orders o
where o.order_delivered_customer_date is not null
) d
join order_reviews rev on rev.order_id = d.order_id
group by 1
"""
def set_reviews_index(create):
with engine.connect() as conn:
conn.execute(text('drop index if exists idx_reviews_order_id'))
if create:
conn.execute(text('create index idx_reviews_order_id on order_reviews(order_id)'))
conn.commit()
_ = compare(
'delivery_query (wide hash join)',
delivery_query,
setup_before=lambda: set_reviews_index(False),
setup_after=lambda: set_reviews_index(True),
)
====================================================================================================
delivery_query (wide hash join) - BEFORE (one representative run of 50)
====================================================================================================
HashAggregate (cost=19743.36..25565.36 rows=95921 width=72) (actual time=116.155..116.206 rows=5 loops=1)
Group Key: CASE WHEN (EXTRACT(day FROM (o.order_delivered_customer_date - o.order_purchase_timestamp)) <= '3'::numeric) THEN '0-3 days'::text WHEN (EXTRACT(day FROM (o.order_delivered_customer_date - o.order_purchase_timestamp)) <= '7'::numeric) THEN '4-7 days'::text WHEN (EXTRACT(day FROM (o.order_delivered_customer_date - o.order_purchase_timestamp)) <= '14'::numeric) THEN '8-14 days'::text WHEN (EXTRACT(day FROM (o.order_delivered_customer_date - o.order_purchase_timestamp)) <= '21'::numeric) THEN '15-21 days'::text ELSE '22+ days'::text END
Planned Partitions: 4 Batches: 1 Memory Usage: 793kB
Buffers: shared hit=3628
-> Hash Join (cost=4051.54..11071.32 rows=96356 width=36) (actual time=27.951..102.519 rows=96359 loops=1)
Hash Cond: ((o.order_id)::text = (rev.order_id)::text)
Buffers: shared hit=3628
-> Seq Scan on orders o (cost=0.00..2803.41 rows=96567 width=49) (actual time=0.006..11.736 rows=96476 loops=1)
Filter: (order_delivered_customer_date IS NOT NULL)
Rows Removed by Filter: 2965
Buffers: shared hit=1809
-> Hash (cost=2811.24..2811.24 rows=99224 width=37) (actual time=27.505..27.506 rows=99224 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 7710kB
Buffers: shared hit=1819
-> Seq Scan on order_reviews rev (cost=0.00..2811.24 rows=99224 width=37) (actual time=0.004..9.437 rows=99224 loops=1)
Buffers: shared hit=1819
Planning:
Buffers: shared hit=8
Planning Time: 0.240 ms
Execution Time: 117.490 ms
====================================================================================================
delivery_query (wide hash join) - AFTER (one representative run of 50)
====================================================================================================
HashAggregate (cost=19743.36..25565.36 rows=95921 width=72) (actual time=116.650..116.698 rows=5 loops=1)
Group Key: CASE WHEN (EXTRACT(day FROM (o.order_delivered_customer_date - o.order_purchase_timestamp)) <= '3'::numeric) THEN '0-3 days'::text WHEN (EXTRACT(day FROM (o.order_delivered_customer_date - o.order_purchase_timestamp)) <= '7'::numeric) THEN '4-7 days'::text WHEN (EXTRACT(day FROM (o.order_delivered_customer_date - o.order_purchase_timestamp)) <= '14'::numeric) THEN '8-14 days'::text WHEN (EXTRACT(day FROM (o.order_delivered_customer_date - o.order_purchase_timestamp)) <= '21'::numeric) THEN '15-21 days'::text ELSE '22+ days'::text END
Planned Partitions: 4 Batches: 1 Memory Usage: 793kB
Buffers: shared hit=3628
-> Hash Join (cost=4051.54..11071.32 rows=96356 width=36) (actual time=27.566..102.477 rows=96359 loops=1)
Hash Cond: ((o.order_id)::text = (rev.order_id)::text)
Buffers: shared hit=3628
-> Seq Scan on orders o (cost=0.00..2803.41 rows=96567 width=49) (actual time=0.006..11.478 rows=96476 loops=1)
Filter: (order_delivered_customer_date IS NOT NULL)
Rows Removed by Filter: 2965
Buffers: shared hit=1809
-> Hash (cost=2811.24..2811.24 rows=99224 width=37) (actual time=27.109..27.109 rows=99224 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 7710kB
Buffers: shared hit=1819
-> Seq Scan on order_reviews rev (cost=0.00..2811.24 rows=99224 width=37) (actual time=0.004..9.840 rows=99224 loops=1)
Buffers: shared hit=1819
Planning:
Buffers: shared hit=16
Planning Time: 0.301 ms
Execution Time: 118.081 ms
--- 50 runs each, after warm-up ---
before: median=124.56ms IQR=[118.8, 132.7] buffers=3628
after : median=117.90ms IQR=[116.0, 120.0] buffers=3628
mann-whitney U p-value = 2.216e-06
This doesn't mean that the index is pointless, it just means that the wide join is the wrong query to use it on. Benchmarked properly (n=50, median, Mann-Whitney), the wide join shows buffers are the same before and after (3,628), confirming the index plays no part in this plan. The timing difference does come back statistically significant this time (p=0.0114), but with buffers unchanged, that just shows a large enough sample can pick up a tiny, practically meaningless timing gap. It's the buffer count, not the p-value, that tells you the index isn't doing any work here.
A foreign key index usually earns its keep on the opposite kind of query, like a single row lookup. Pulling the review for one specific order (the kind of thing an application would do constantly) gives the same benchmark a completely different result. Median time drops from 7.72 ms to 0.04 ms, with p=6.7e-18, about as clear as a p-value gets, and the buffer count collapses from 1,819 down to 4. Without the index, postgres scans the whole table to find the one row it wants. With it, an Index Scan goes straight there and reads almost nothing else.
From the two examples, the foreign key index does nothing for a wide analytical join and almost everything for a targeted lookup. Now both the plan and the statistics agree on that rather than just one anecdotal run. Working out which one you've actually got is a very important thing to do before adding it. The indexes overview in the docs makes the same point that an index isn't free and isn't always a win.
lookup_query = """
select review_id, review_score, order_id
from order_reviews
where order_id = 'a548910a1c6147796b98fdf73dbeba33'
"""
_ = compare(
'lookup_query (single-row lookup)',
lookup_query,
setup_before=lambda: set_reviews_index(False),
setup_after=lambda: set_reviews_index(True),
)
==================================================================================================== lookup_query (single-row lookup) - BEFORE (one representative run of 50) ==================================================================================================== Seq Scan on order_reviews (cost=0.00..3059.30 rows=1 width=70) (actual time=0.008..7.271 rows=1 loops=1) Filter: ((order_id)::text = 'a548910a1c6147796b98fdf73dbeba33'::text) Rows Removed by Filter: 99223 Buffers: shared hit=1819 Planning Time: 0.040 ms Execution Time: 7.282 ms ==================================================================================================== lookup_query (single-row lookup) - AFTER (one representative run of 50) ==================================================================================================== Index Scan using idx_reviews_order_id on order_reviews (cost=0.42..8.44 rows=1 width=70) (actual time=0.017..0.017 rows=1 loops=1) Index Cond: ((order_id)::text = 'a548910a1c6147796b98fdf73dbeba33'::text) Buffers: shared hit=4 Planning Time: 0.020 ms Execution Time: 0.032 ms --- 50 runs each, after warm-up --- before: median=7.72ms IQR=[7.5, 7.9] buffers=1819 after : median=0.04ms IQR=[0.0, 0.0] buffers=4 mann-whitney U p-value = 6.714e-18
Case study 2 - expression index for date_trunc queries¶
Every monthly revenue query in the first notebook groups by date_trunc('month', order_purchase_timestamp), and a plain index on the raw timestamp does nothing for those. The moment you wrap a column in a function, postgres can't match it against an index built on the bare column.
An expression index is meant to fix that. Instead of indexing the column, you index the result of the function, so the pre-computed month values are what actually sit in the index.
create index idx_orders_purchase_month
on orders(date_trunc('month', order_purchase_timestamp));
What is unexpected is that building the index isn't enough on its own. The cell below runs the full monthly revenue query before and after creating it, and the plan doesn't budge. Same Parallel Seq Scan on orders, same sort, same hash join, and the index nowhere in the plan. The reason is that the query aggregates every month at once, so it has to read essentially the whole table anyway, and a straight sequential scan beats walking an index for that. The index also only stores the month value, not order_id or price, so even if postgres used it, it would still have to go back to the table for the join. It correctly decided the index wasn't worth it.
# the full-table monthly query (the one every monthly revenue query looks like)
full_table_query = """
select
date_trunc('month', o.order_purchase_timestamp)::date as month,
round(sum(oi.price), 2) as revenue
from orders o
join order_items oi on oi.order_id = o.order_id
where o.order_purchase_timestamp is not null
group by 1
order by 1
"""
def set_purchase_month_index(create):
with engine.connect() as conn:
conn.execute(text('drop index if exists idx_orders_purchase_month'))
if create:
conn.execute(text(
"create index idx_orders_purchase_month "
"on orders(date_trunc('month', order_purchase_timestamp))"
))
conn.execute(text('analyze orders')) #! refresh stats so the planner can cost the expression index
conn.commit()
_ = compare(
'full_table_query (reads every month)',
full_table_query,
setup_before=lambda: set_purchase_month_index(False),
setup_after=lambda: set_purchase_month_index(True),
)
====================================================================================================
full_table_query (reads every month) - BEFORE (one representative run of 50)
====================================================================================================
Finalize GroupAggregate (cost=12766.56..24345.29 rows=98515 width=36) (actual time=76.190..87.957 rows=24 loops=1)
Group Key: ((date_trunc('month'::text, o.order_purchase_timestamp))::date)
Buffers: shared hit=4050, temp read=283 written=285
-> Gather Merge (cost=12766.56..21878.00 rows=66265 width=36) (actual time=76.142..87.902 rows=46 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=4050, temp read=283 written=285
-> Partial GroupAggregate (cost=11766.55..13423.18 rows=66265 width=36) (actual time=63.006..73.557 rows=23 loops=2)
Group Key: ((date_trunc('month'::text, o.order_purchase_timestamp))::date)
Buffers: shared hit=4050, temp read=283 written=285
-> Sort (cost=11766.55..11932.21 rows=66265 width=10) (actual time=62.998..67.429 rows=56325 loops=2)
Sort Key: ((date_trunc('month'::text, o.order_purchase_timestamp))::date)
Sort Method: external merge Disk: 1168kB
Buffers: shared hit=4050, temp read=283 written=285
Worker 0: Sort Method: external merge Disk: 1096kB
-> Parallel Hash Join (cost=3125.13..6460.06 rows=66265 width=10) (actual time=17.729..49.824 rows=56325 loops=2)
Hash Cond: ((oi.order_id)::text = (o.order_id)::text)
Buffers: shared hit=3993
-> Parallel Seq Scan on order_items oi (cost=0.00..2829.65 rows=66265 width=39) (actual time=0.007..3.154 rows=56325 loops=2)
Buffers: shared hit=2167
-> Parallel Hash (cost=2393.95..2393.95 rows=58495 width=41) (actual time=17.089..17.090 rows=49720 loops=2)
Buckets: 131072 Batches: 1 Memory Usage: 8832kB
Buffers: shared hit=1809
-> Parallel Seq Scan on orders o (cost=0.00..2393.95 rows=58495 width=41) (actual time=0.009..5.352 rows=49720 loops=2)
Filter: (order_purchase_timestamp IS NOT NULL)
Buffers: shared hit=1809
Planning:
Buffers: shared hit=16
Planning Time: 0.359 ms
Execution Time: 88.184 ms
====================================================================================================
full_table_query (reads every month) - AFTER (one representative run of 50)
====================================================================================================
Finalize GroupAggregate (cost=12766.56..24343.83 rows=98442 width=36) (actual time=74.850..91.470 rows=24 loops=1)
Group Key: ((date_trunc('month'::text, o.order_purchase_timestamp))::date)
Buffers: shared hit=4050, temp read=283 written=284
-> Gather Merge (cost=12766.56..21878.00 rows=66265 width=36) (actual time=74.798..91.392 rows=46 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=4050, temp read=283 written=284
-> Partial GroupAggregate (cost=11766.55..13423.18 rows=66265 width=36) (actual time=62.906..75.186 rows=23 loops=2)
Group Key: ((date_trunc('month'::text, o.order_purchase_timestamp))::date)
Buffers: shared hit=4050, temp read=283 written=284
-> Sort (cost=11766.55..11932.21 rows=66265 width=10) (actual time=62.897..67.921 rows=56325 loops=2)
Sort Key: ((date_trunc('month'::text, o.order_purchase_timestamp))::date)
Sort Method: external merge Disk: 1208kB
Buffers: shared hit=4050, temp read=283 written=284
Worker 0: Sort Method: external merge Disk: 1056kB
-> Parallel Hash Join (cost=3125.13..6460.06 rows=66265 width=10) (actual time=16.380..49.356 rows=56325 loops=2)
Hash Cond: ((oi.order_id)::text = (o.order_id)::text)
Buffers: shared hit=3993
-> Parallel Seq Scan on order_items oi (cost=0.00..2829.65 rows=66265 width=39) (actual time=0.006..3.449 rows=56325 loops=2)
Buffers: shared hit=2167
-> Parallel Hash (cost=2393.95..2393.95 rows=58495 width=41) (actual time=15.803..15.803 rows=49720 loops=2)
Buckets: 131072 Batches: 1 Memory Usage: 8832kB
Buffers: shared hit=1809
-> Parallel Seq Scan on orders o (cost=0.00..2393.95 rows=58495 width=41) (actual time=0.009..5.011 rows=49720 loops=2)
Filter: (order_purchase_timestamp IS NOT NULL)
Buffers: shared hit=1809
Planning:
Buffers: shared hit=16
Planning Time: 0.294 ms
Execution Time: 91.686 ms
--- 50 runs each, after warm-up ---
before: median=87.14ms IQR=[85.8, 91.8] buffers=4050
after : median=85.74ms IQR=[85.1, 86.9] buffers=4050
mann-whitney U p-value = 0.0003075
The index isn't useless though. It just needs a query that wants a small slice of the table rather than all of it. Here's the same date_trunc('month', ...) grouping pointed at a single month instead of every month at once.
This time the outcome is different. Postgres switches to a Bitmap Index Scan on idx_orders_purchase_month and jumps straight to January 2018's rows, instead of scanning all of orders and throwing 46k of them away. Benchmarked properly at n=50, median time is 23.70 ms before and 24.09 ms after, basically the same, and a Mann-Whitney test agrees there's no real difference (p=0.17). The buffer count tells a completely different story though, 30,855 down to 3,959, about 7.8x fewer.
Looking more into this, the reason the clock doesn't move is that every one of those buffers is already a cache hit, not a disk read. All the pages are sitting in memory either way, so reading 30,855 of them versus 3,959 just means touching memory a few thousand more times, which costs almost nothing. The index is genuinely doing 7.8x less work, but that work was cheap to begin with, so the saving disappears into normal noise on the clock. If this table were big enough that those extra buffers meant real disk reads instead of cache hits, the timing difference would show up.
So an expression index is only half the story, selectivity is the other half, and there's a layer underneath that. The buffer count is what proves the index is doing less work, while the clock only shows it once that work would have been expensive anyway. The postgres expression index docs cover when it's worth it and what it costs to maintain one.
# the same idea but selective - a single month instead of all of them
selective_query = """
select round(sum(oi.price), 2) as revenue
from orders o
join order_items oi on oi.order_id = o.order_id
where date_trunc('month', o.order_purchase_timestamp) = '2018-01-01'
"""
_ = compare(
'selective_query (one month)',
selective_query,
setup_before=lambda: set_purchase_month_index(False),
setup_after=lambda: set_purchase_month_index(True),
)
====================================================================================================
selective_query (one month) - BEFORE (one representative run of 50)
====================================================================================================
Finalize Aggregate (cost=5760.84..5760.85 rows=1 width=32) (actual time=21.063..23.714 rows=1 loops=1)
Buffers: shared hit=30855
-> Gather (cost=5760.72..5760.83 rows=1 width=32) (actual time=20.895..23.706 rows=2 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=30855
-> Partial Aggregate (cost=4760.72..4760.73 rows=1 width=32) (actual time=18.209..18.211 rows=1 loops=2)
Buffers: shared hit=30855
-> Nested Loop (cost=0.42..4759.89 rows=331 width=6) (actual time=0.047..17.737 rows=4104 loops=2)
Buffers: shared hit=30855
-> Parallel Seq Scan on orders o (cost=0.00..2686.42 rows=292 width=33) (actual time=0.009..6.568 rows=3634 loops=2)
Filter: (date_trunc('month'::text, order_purchase_timestamp) = '2018-01-01 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 46086
Buffers: shared hit=1809
-> Index Scan using order_items_pkey on order_items oi (cost=0.42..7.09 rows=1 width=39) (actual time=0.003..0.003 rows=1 loops=7269)
Index Cond: ((order_id)::text = (o.order_id)::text)
Buffers: shared hit=29046
Planning:
Buffers: shared hit=16
Planning Time: 0.254 ms
Execution Time: 23.763 ms
====================================================================================================
selective_query (one month) - AFTER (one representative run of 50)
====================================================================================================
Aggregate (cost=5699.41..5699.42 rows=1 width=32) (actual time=23.584..23.587 rows=1 loops=1)
Buffers: shared hit=3959
-> Hash Join (cost=2089.89..5679.11 rows=8118 width=6) (actual time=3.761..22.932 rows=8208 loops=1)
Hash Cond: ((oi.order_id)::text = (o.order_id)::text)
Buffers: shared hit=3959
-> Seq Scan on order_items oi (cost=0.00..3293.50 rows=112650 width=39) (actual time=0.003..5.126 rows=112650 loops=1)
Buffers: shared hit=2167
-> Hash (cost=2000.32..2000.32 rows=7166 width=33) (actual time=3.721..3.722 rows=7269 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 526kB
Buffers: shared hit=1792
-> Bitmap Heap Scan on orders o (cost=83.83..2000.32 rows=7166 width=33) (actual time=0.494..2.455 rows=7269 loops=1)
Recheck Cond: (date_trunc('month'::text, order_purchase_timestamp) = '2018-01-01 00:00:00'::timestamp without time zone)
Heap Blocks: exact=1784
Buffers: shared hit=1792
-> Bitmap Index Scan on idx_orders_purchase_month (cost=0.00..82.04 rows=7166 width=0) (actual time=0.342..0.343 rows=7269 loops=1)
Index Cond: (date_trunc('month'::text, order_purchase_timestamp) = '2018-01-01 00:00:00'::timestamp without time zone)
Buffers: shared hit=8
Planning:
Buffers: shared hit=16
Planning Time: 0.275 ms
Execution Time: 23.851 ms
--- 50 runs each, after warm-up ---
before: median=23.70ms IQR=[23.2, 24.2] buffers=30855
after : median=24.09ms IQR=[23.5, 24.5] buffers=3959
mann-whitney U p-value = 0.1744
There's a more specific reason for the noise too. The plan without the index is a Nested Loop that probes order_items_pkey once per matching row, about 7,270 individual probes. Each probe is its own small unit of scheduling and lock overhead, and 7,270 of them add up to real run-to-run variance even when every probe is a cache hit. My first guess was stale statistics on orders, so I tried running analyze orders before timing the no-index case, but it didn't move the plan or the row estimate at all. Postgres still guessed 331 rows back instead of the actual ~8,200, because there's no way to get a good estimate for a function-wrapped predicate without either the expression index or create statistics. Analyzing the raw column doesn't help. The variance comes from the shape of the plan itself and not from missing stats.
Case study 3 - covering index to skip heap fetches¶
Even with an index on order_payments.order_id, postgres still has to go back to the main table to read payment_value for every matched row. Each of those trips is a random read, what postgres calls a heap fetch, and they add up once the table is large.
A covering index is meant to remove them. The include clause tucks extra columns inside the index without using them for the lookup itself, so postgres can hand back the value straight from the index and never touch the table. When it manages that, the plan comes out as an Index Only Scan with Heap Fetches: 0.
create index idx_payments_covering
on order_payments(order_id)
include (payment_value);
But like the other two, the index only helps if the query lets it. The cell below runs the RFM query before and after creating the covering index, and the plan stays a Seq Scan on order_payments both times. The reason is coverage. The RFM query joins to orders and needs customer_id and the order timestamp, columns the index doesn't carry, so an index-only scan is off the table from the start. Postgres falls back to reading the whole table.
# the rfm query - joins to orders and needs customer columns, so the index cant cover it
rfm_query = """
select
o.customer_id,
max(o.order_purchase_timestamp) as last_order,
count(distinct o.order_id) as frequency,
round(sum(op.payment_value), 2) as monetary
from orders o
join order_payments op on op.order_id = o.order_id
group by o.customer_id
"""
def set_payments_covering_index(create, vacuum=False):
with engine.connect() as conn:
conn.execute(text('drop index if exists idx_payments_covering'))
if create:
conn.execute(text(
'create index idx_payments_covering '
'on order_payments(order_id) include (payment_value)'
))
conn.commit()
if vacuum:
# vacuum cant run inside a transaction, so use an autocommit connection
with engine.connect().execution_options(isolation_level='AUTOCOMMIT') as conn:
conn.execute(text('vacuum analyze order_payments')) #! vacuum sets the visibility map, required for heap fetches: 0
_ = compare(
'rfm_query (needs orders columns the index cant cover)',
rfm_query,
setup_before=lambda: set_payments_covering_index(False),
setup_after=lambda: set_payments_covering_index(True),
)
====================================================================================================
rfm_query (needs orders columns the index cant cover) - BEFORE (one representative run of 50)
====================================================================================================
GroupAggregate (cost=23961.61..26751.80 rows=99441 width=81) (actual time=199.508..353.817 rows=99440 loops=1)
Group Key: o.customer_id
Buffers: shared hit=2990, temp read=2238 written=2241
-> Sort (cost=23961.61..24221.32 rows=103886 width=80) (actual time=199.484..224.786 rows=103886 loops=1)
Sort Key: o.customer_id
Sort Method: external merge Disk: 9960kB
Buffers: shared hit=2990, temp read=2238 written=2241
-> Hash Join (cost=5309.42..10688.99 rows=103886 width=80) (actual time=30.988..93.280 rows=103886 loops=1)
Hash Cond: ((op.order_id)::text = (o.order_id)::text)
Buffers: shared hit=2990, temp read=993 written=993
-> Seq Scan on order_payments op (cost=0.00..2219.86 rows=103886 width=39) (actual time=0.005..8.365 rows=103886 loops=1)
Buffers: shared hit=1181
-> Hash (cost=2803.41..2803.41 rows=99441 width=74) (actual time=30.506..30.506 rows=99441 loops=1)
Buckets: 131072 Batches: 2 Memory Usage: 6515kB
Buffers: shared hit=1809, temp written=612
-> Seq Scan on orders o (cost=0.00..2803.41 rows=99441 width=74) (actual time=0.010..8.935 rows=99441 loops=1)
Buffers: shared hit=1809
Planning:
Buffers: shared hit=16
Planning Time: 0.253 ms
Execution Time: 358.941 ms
====================================================================================================
rfm_query (needs orders columns the index cant cover) - AFTER (one representative run of 50)
====================================================================================================
GroupAggregate (cost=23961.61..26751.80 rows=99441 width=81) (actual time=202.207..355.369 rows=99440 loops=1)
Group Key: o.customer_id
Buffers: shared hit=2990, temp read=2238 written=2241
-> Sort (cost=23961.61..24221.32 rows=103886 width=80) (actual time=202.187..226.905 rows=103886 loops=1)
Sort Key: o.customer_id
Sort Method: external merge Disk: 9960kB
Buffers: shared hit=2990, temp read=2238 written=2241
-> Hash Join (cost=5309.42..10688.99 rows=103886 width=80) (actual time=31.394..95.055 rows=103886 loops=1)
Hash Cond: ((op.order_id)::text = (o.order_id)::text)
Buffers: shared hit=2990, temp read=993 written=993
-> Seq Scan on order_payments op (cost=0.00..2219.86 rows=103886 width=39) (actual time=0.005..8.688 rows=103886 loops=1)
Buffers: shared hit=1181
-> Hash (cost=2803.41..2803.41 rows=99441 width=74) (actual time=30.924..30.925 rows=99441 loops=1)
Buckets: 131072 Batches: 2 Memory Usage: 6515kB
Buffers: shared hit=1809, temp written=612
-> Seq Scan on orders o (cost=0.00..2803.41 rows=99441 width=74) (actual time=0.008..9.120 rows=99441 loops=1)
Buffers: shared hit=1809
Planning:
Buffers: shared hit=16
Planning Time: 0.278 ms
Execution Time: 360.470 ms
--- 50 runs each, after warm-up ---
before: median=371.86ms IQR=[363.0, 384.5] buffers=2990
after : median=365.09ms IQR=[360.5, 377.2] buffers=2990
mann-whitney U p-value = 0.08543
The covering index works when the query needs only the columns the index actually carries. Strip the question down to just order_id and payment_value (the two columns in the index) and there's no longer any reason to visit the table.
Which is exactly what happens, postgres switches to an Index Only Scan with Heap Fetches: 0, confirming the main table was never touched. The buffer counts back that up clearly. 104,762 buffers before versus 746 after (~140x fewer), and that ratio holds rock solid on every run since buffer counts don't move between runs of the same plan. This time the clock agrees too, the median time drops from 77.31 ms to 51.75 ms and a Mann-Whitney test puts that at p=2.0e-16. Unlike case study 2 the I/O saved here is large enough that it shows up clearly on both measures at once.
It is worth noting the gap between the two ratios though. Buffers fall by 140x but the clock only drops by about 1.5x. Even before the covering index existed, postgres was already using order_payments_pkey to scan in order_id order rather than sorting it separately, so neither plan pays a sort cost. The 140x fewer buffers come entirely from skipping the heap fetch on every row, but that doesn't scale into a 140x cut in clock time the way it might seem to.
So the third indexing is different again from the first two. Case 1 turned on selectivity and case 2 on a function-wrapped column, but a covering index is about coverage. It pays off only when the query's columns all live in the index, and it's the heap fetches (not the row count) that decide whether it was worth it. The index-only scans docs go into when postgres can actually pull this off because it isn't always.
# the same table, but a query that needs only the covered columns (order_id, payment_value)
covered_query = """
select order_id, sum(payment_value)
from order_payments
group by order_id
"""
_ = compare(
'covered_query (only needs columns the index carries)',
covered_query,
setup_before=lambda: set_payments_covering_index(False),
setup_after=lambda: set_payments_covering_index(True, vacuum=True),
)
====================================================================================================
covered_query (only needs columns the index carries) - BEFORE (one representative run of 50)
====================================================================================================
GroupAggregate (cost=0.42..11935.82 rows=94560 width=65) (actual time=0.025..72.784 rows=99440 loops=1)
Group Key: order_id
Buffers: shared hit=104762
-> Index Scan using order_payments_pkey on order_payments (cost=0.42..10234.39 rows=103886 width=39) (actual time=0.018..34.487 rows=103886 loops=1)
Buffers: shared hit=104762
Planning Time: 0.051 ms
Execution Time: 75.623 ms
====================================================================================================
covered_query (only needs columns the index carries) - AFTER (one representative run of 50)
====================================================================================================
GroupAggregate (cost=0.42..6268.61 rows=94598 width=65) (actual time=0.024..48.127 rows=99440 loops=1)
Group Key: order_id
Buffers: shared hit=746
-> Index Only Scan using idx_payments_covering on order_payments (cost=0.42..4566.71 rows=103886 width=39) (actual time=0.018..7.860 rows=103886 loops=1)
Heap Fetches: 0
Buffers: shared hit=746
Planning Time: 0.055 ms
Execution Time: 51.014 ms
--- 50 runs each, after warm-up ---
before: median=77.31ms IQR=[75.8, 79.5] buffers=104762
after : median=51.75ms IQR=[51.3, 53.8] buffers=746
mann-whitney U p-value = 2.02e-16
12. Advanced SQL techniques¶
With the core analysis behind me, I wanted to play with a few patterns that come up a lot in SQL but I'd never had a real reason to use. Each one below is built around an actual question from the OLIST data, since a technique sticks a lot better when there's a real problem behind it instead of a toy example.
Technique 1 - crosstab pivot table¶
What I wanted here was monthly revenue split across the top 5 states, as a matrix with months down the side and states across the top, instead of the long list of (month, state, revenue) triples a normal group by hands back.
crosstab is postgres's native pivot function. It rotates the distinct values of one column into separate output columns. It lives in the tablefunc extension, which ships with postgres but has to be switched on by hand with create extension.
The catch is that you have to declare the output columns up front. You can't pivot over a dynamic set of values, they have to be hardcoded in the query. That's fine for a fixed set of states but awkward for anything that changes often. When the set isn't fixed, the filter aggregate in technique 2 is the more flexible option.
The matrix shows something that's genuinely hard to see in a long list. SP grows faster than the others through 2017 and stays dominant, MG and RS both pick up through Q3 and Q4, and the november 2017 spike lands across all five states at once, confirming it was a platform-wide event rather than a regional one. The tablefunc docs have the full signature, which is a bit fiddly the first time through.
import numpy as np
from sqlalchemy import text
# Enable the tablefunc extension (idempotent)
with engine.connect() as conn:
conn.execute(text('create extension if not exists tablefunc'))
conn.commit()
df_ct = q("""
select
month,
coalesce(sp, 0) as sp,
coalesce(rj, 0) as rj,
coalesce(mg, 0) as mg,
coalesce(rs, 0) as rs,
coalesce(pr, 0) as pr
from crosstab(
$$
select
to_char(date_trunc('month', o.order_purchase_timestamp), 'YYYY-MM') as month,
c.customer_state as state,
round(sum(op.payment_value)::numeric, 0) as revenue
from orders o
join customers c on c.customer_id = o.customer_id
join order_payments op on op.order_id = o.order_id
where c.customer_state in ('SP', 'RJ', 'MG', 'RS', 'PR')
and o.order_purchase_timestamp >= '2017-01-01'
and o.order_purchase_timestamp < '2018-09-01'
group by 1, 2
order by 1, 2
$$,
$$ select unnest(array['SP', 'RJ', 'MG', 'RS', 'PR']) $$
) as ct(month text, sp numeric, rj numeric, mg numeric, rs numeric, pr numeric)
order by month
""")
states = ['sp', 'rj', 'mg', 'rs', 'pr']
heat = df_ct.set_index('month')[states].astype(float)
heat.columns = [s.upper() for s in heat.columns]
annot = heat.map(lambda v: f'R${v/1e3:.0f}k') #! applymap was removed in pandas 3.0, use map instead
fig, ax = plt.subplots(figsize=(11, 9))
sns.heatmap(
heat, cmap='Blues', annot=annot, fmt='', annot_kws={'fontsize': 8},
cbar_kws={'label': 'Revenue (R$)'}, linewidths=0, ax=ax,
)
ax.set_xlabel('')
ax.set_ylabel('')
plt.setp(ax.get_xticklabels(), rotation=0, fontweight='bold', fontsize=12)
plt.setp(ax.get_yticklabels(), rotation=0, fontsize=9)
cbar = ax.collections[0].colorbar
cbar.ax.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'R${x/1e3:.0f}k'))
ax.set_title('Monthly Revenue Heatmap: Top 5 States (Jan 2017 - Aug 2018)',
fontsize=13, fontweight='bold', pad=15)
plt.tight_layout()
plt.show()
df_ct
| month | sp | rj | mg | rs | pr | |
|---|---|---|---|---|---|---|
| 0 | 2017-01 | 47371.0 | 14933.0 | 18831.0 | 13744.0 | 6483.0 |
| 1 | 2017-02 | 92522.0 | 39013.0 | 35978.0 | 18396.0 | 21821.0 |
| 2 | 2017-03 | 161606.0 | 64617.0 | 52139.0 | 24297.0 | 18679.0 |
| 3 | 2017-04 | 152308.0 | 66462.0 | 47117.0 | 22377.0 | 18456.0 |
| 4 | 2017-05 | 204600.0 | 85793.0 | 59492.0 | 33276.0 | 36312.0 |
| 5 | 2017-06 | 201360.0 | 63797.0 | 55371.0 | 28976.0 | 25956.0 |
| 6 | 2017-07 | 214428.0 | 92942.0 | 65960.0 | 35360.0 | 29695.0 |
| 7 | 2017-08 | 230200.0 | 91604.0 | 71051.0 | 40961.0 | 34734.0 |
| 8 | 2017-09 | 251950.0 | 109403.0 | 85040.0 | 43536.0 | 26772.0 |
| 9 | 2017-10 | 262256.0 | 113137.0 | 95263.0 | 44250.0 | 39764.0 |
| 10 | 2017-11 | 417791.0 | 176634.0 | 157769.0 | 70115.0 | 56176.0 |
| 11 | 2017-12 | 325472.0 | 137618.0 | 110880.0 | 49406.0 | 38933.0 |
| 12 | 2018-01 | 430306.0 | 144560.0 | 134886.0 | 57284.0 | 52902.0 |
| 13 | 2018-02 | 358518.0 | 139424.0 | 125611.0 | 55182.0 | 53291.0 |
| 14 | 2018-03 | 441724.0 | 141230.0 | 142357.0 | 68204.0 | 60179.0 |
| 15 | 2018-04 | 452748.0 | 144664.0 | 130598.0 | 61425.0 | 77119.0 |
| 16 | 2018-05 | 492979.0 | 147202.0 | 121913.0 | 60118.0 | 54484.0 |
| 17 | 2018-06 | 396565.0 | 122265.0 | 125773.0 | 53302.0 | 50356.0 |
| 18 | 2018-07 | 383214.0 | 121368.0 | 114679.0 | 53276.0 | 56467.0 |
| 19 | 2018-08 | 459935.0 | 113984.0 | 115323.0 | 52623.0 | 49978.0 |
The november 2017 spike showing up across all five states at once confirms it was platform-wide. If a single state had missed it, that would be worth digging into on its own, maybe a logistics failure, lower marketing reach, or a supply problem in that region.
Technique 2 - filter aggregate as a manual pivot¶
filter is a more portable way to pivot. count(*) filter (where payment_type = 'credit_card') counts only the rows that match the condition, and chaining a few of those together gets you a full pivot with no extension needed at all.
This is one I'll reach for often now. It's cleaner than a case when buried inside an aggregate, and it runs on any sql database rather than just postgres.
I pointed it at the payment type breakdown, how orders split across credit card, boleto (a sort of bank slip), voucher, and debit card, and how average order value differs between them. Credit card dominates the count (74,586 orders versus boleto's 19,191 among delivered orders), and it also has the higher average order value (R$162.24 vs R$144.33). I think that's because brazilian credit card payments are often split into instalments ("parcelamento"), so someone paying over 6 or 12 months can commit to a bigger basket, while boleto needs the full amount upfront. That gap flips for a couple of the smaller order statuses, so it's not a universal rule, just the pattern where most of the volume sits. The filter clause is documented alongside the other aggregate expressions.
df_pivot = q("""
select
o.order_status,
count(*) filter (where op.payment_type = 'credit_card') as credit_card_orders,
count(*) filter (where op.payment_type = 'boleto') as boleto_orders,
count(*) filter (where op.payment_type = 'voucher') as voucher_orders,
count(*) filter (where op.payment_type = 'debit_card') as debit_card_orders,
round(avg(op.payment_value) filter (where op.payment_type = 'credit_card')::numeric, 2) as avg_cc_value,
round(avg(op.payment_value) filter (where op.payment_type = 'boleto')::numeric, 2) as avg_boleto_value
from orders o
join order_payments op on op.order_id = o.order_id
group by o.order_status
order by count(*) desc
""")
pay_cols = ['credit_card_orders', 'boleto_orders', 'voucher_orders', 'debit_card_orders']
labels = ['Credit Card', 'Boleto', 'Voucher', 'Debit Card']
colors = [PRIMARY, '#1E88E5', MUTED, ACCENT]
# Stacked bars via seaborn: draw cumulative totals from largest to smallest so
# each successive layer covers the prior - reproduces a stacked look using
# only sns.barplot calls.
df_long = df_pivot.set_index('order_status')[pay_cols].fillna(0)
cum = df_long.cumsum(axis=1)
fig, ax = plt.subplots(figsize=(12, 5))
for col, color in zip(reversed(pay_cols), reversed(colors)):
sns.barplot(x=cum.index, y=cum[col].values, color=color,
ax=ax, width=0.5)
handles = [plt.Rectangle((0, 0), 1, 1, color=c) for c in colors]
ax.legend(handles, labels, loc='upper right', fontsize=9)
ax.set_title('Payment Type Mix by Order Status (FILTER aggregate pivot)')
ax.set_ylabel('Number of Payments')
ax.set_xlabel('')
ax.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'{x:,.0f}'))
plt.tight_layout()
plt.show()
df_pivot
| order_status | credit_card_orders | boleto_orders | voucher_orders | debit_card_orders | avg_cc_value | avg_boleto_value | |
|---|---|---|---|---|---|---|---|
| 0 | delivered | 74586 | 19191 | 5493 | 1486 | 162.24 | 144.33 |
| 1 | shipped | 851 | 209 | 84 | 22 | 171.79 | 115.92 |
| 2 | canceled | 444 | 95 | 115 | 7 | 219.31 | 184.25 |
| 3 | unavailable | 446 | 150 | 47 | 6 | 212.26 | 167.03 |
| 4 | invoiced | 239 | 67 | 13 | 6 | 213.78 | 228.82 |
| 5 | processing | 224 | 70 | 23 | 2 | 227.25 | 244.80 |
| 6 | created | 3 | 2 | 0 | 0 | 170.89 | 87.72 |
| 7 | approved | 2 | 0 | 0 | 0 | 120.54 | NaN |
The payment mix barely shifts by order status, which is useful to know on its own. Payment type isn't a strong predictor of whether an order gets cancelled or never delivered.
Technique 3 - lateral join for top n per group¶
The question I wanted to answer here is simple. For each of the top 10 customers by lifetime value, show their 3 most recent orders.
The usual way to do this is row_number() over (partition by ...), but that means wrapping the whole thing in a subquery just to filter on the row number. lateral is cleaner. The subquery on the right side of the join runs once for every row of the outer query, can reference columns from that row, and can return several rows of its own.
What comes out lines up with something section 5 of the first notebook already hinted at. The top 10 customers by lifetime spend all basically made one big order, and there are no high-frequency repeat buyers among them. In this dataset, high lifetime value just means someone made one large purchase, which is a very different customer from the kind you'd build a loyalty programme around. The postgres lateral docs have a good worked example if the once-per-row idea doesn't click straight away.
df_lateral = q("""
with top_customers as (
select
o.customer_id,
round(sum(op.payment_value)::numeric, 2) as lifetime_value
from orders o
join order_payments op on op.order_id = o.order_id
group by o.customer_id
order by lifetime_value desc
limit 10
)
select
tc.customer_id,
tc.lifetime_value,
recent.order_id,
recent.order_purchase_timestamp::date as order_date,
recent.order_status,
recent.order_value
from top_customers tc
cross join lateral (
select
o.order_id,
o.order_purchase_timestamp,
o.order_status,
round(sum(op.payment_value)::numeric, 2) as order_value
from orders o
join order_payments op on op.order_id = o.order_id
where o.customer_id = tc.customer_id
group by o.order_id, o.order_purchase_timestamp, o.order_status
order by o.order_purchase_timestamp desc
limit 3
) recent
order by tc.lifetime_value desc, recent.order_purchase_timestamp desc
""")
print(f"Rows returned: {len(df_lateral)} "
f"(10 customers x up to 3 orders each)")
df_lateral
Rows returned: 10 (10 customers x up to 3 orders each)
| customer_id | lifetime_value | order_id | order_date | order_status | order_value | |
|---|---|---|---|---|---|---|
| 0 | 1617b1357756262bfa56ab541c47bc16 | 13664.08 | 03caa2c082116e1d31e67e9ae3700499 | 2017-09-29 | delivered | 13664.08 |
| 1 | ec5b2ba62e574342386871631fafd3fc | 7274.88 | 736e1922ae60d0d6a89247b851902527 | 2018-07-15 | delivered | 7274.88 |
| 2 | c6e2731c5b391845f6800c97401a43a9 | 6929.31 | 0812eb902a67711a1cb742b3cdaa65ae | 2017-02-12 | delivered | 6929.31 |
| 3 | f48d464a0baaea338cb25f816991ab1f | 6922.21 | fefacc66af859508bf1a7934eab1e97f | 2018-07-25 | delivered | 6922.21 |
| 4 | 3fd6777bbce08a352fddd04e4a7cc8f6 | 6726.66 | f5136e38d1a14a4dbd87dff67da82701 | 2017-05-24 | delivered | 6726.66 |
| 5 | 05455dfa7cd02f13d132aa7a6a9729c6 | 6081.54 | 2cc9089445046817a7539d90805e6e5a | 2017-11-24 | delivered | 6081.54 |
| 6 | df55c14d1476a9a3467f131269c2477f | 4950.34 | a96610ab360d42a2e5335a3998b4718a | 2017-04-01 | delivered | 4950.34 |
| 7 | e0a2412720e9ea4f26c1ac985f6a7358 | 4809.44 | b4c4b76c642808cbe472a32b86cddc95 | 2018-07-12 | canceled | 4809.44 |
| 8 | 24bbf5fd2f2e1b359ee7de94defc4a15 | 4764.34 | 199af31afc78c699f0dbf71fb178d4d4 | 2017-04-18 | delivered | 4764.34 |
| 9 | 3d979689f636322c62418b6346b1c6d2 | 4681.78 | 8dbc85d1447242f3b127dda390d56e19 | 2018-06-22 | delivered | 4681.78 |
The output confirms what section 5 of the first notebook suggested. Every top-10 customer by lifetime value basically made one large order, and there just aren't many high-value repeat buyers in this dataset.
Technique 4 - recursive cte for a date spine¶
A plain group by date_trunc('month', ...) quietly drops any month with no data. That leaves holes in charts and breaks anything that expects a complete time series.
The fix is to build a full list of months first and then left join the actual data onto it. A recursive cte generates that list. The anchor starts at the first month, and the recursive step adds one month at a time until it reaches the last.
Postgres also has generate_series, which does the same job in a single line.
select generate_series(
date_trunc('month', min(order_purchase_timestamp)),
date_trunc('month', max(order_purchase_timestamp)),
'1 month'
)::date as month
from orders
In practice generate_series is the better choice here and it's what I'd actually reach for. The recursive cte is still worth knowing for when portability matters, since it's standard sql that runs on bigquery, snowflake, sql server, and the rest. Running it on the OLIST data finds 26 months in the full date range, 2 of them with no orders at all, exactly the kind of gap a plain group by would have silently dropped. The recursive query docs go deep on how the anchor and recursive parts fit together, and generate_series is covered in the set-returning functions docs.
df_spine = q("""
with recursive date_spine as (
select date_trunc('month', min(order_purchase_timestamp))::date as month
from orders
union all
select (month + interval '1 month')::date
from date_spine
where month < (
select date_trunc('month', max(order_purchase_timestamp))::date from orders
)
),
monthly as (
select
date_trunc('month', o.order_purchase_timestamp)::date as month,
round(sum(oi.price)::numeric, 2) as revenue
from orders o
join order_items oi on oi.order_id = o.order_id
where o.order_purchase_timestamp is not null
group by 1
)
select
ds.month,
coalesce(m.revenue, 0) as revenue,
case when m.revenue is null then 'missing' else 'ok' end as status
from date_spine ds
left join monthly m on m.month = ds.month
order by ds.month
""")
df_spine['month'] = pd.to_datetime(df_spine['month'])
df_spine['month_lbl'] = df_spine['month'].dt.strftime('%Y-%m')
fig, ax = plt.subplots(figsize=(13, 5))
sns.barplot(data=df_spine, x='month_lbl', y='revenue', hue='status',
palette={'ok': PRIMARY, 'missing': ACCENT}, dodge=False,
ax=ax, legend=False)
ax.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'R${x/1e3:.0f}k'))
ax.set_title('Revenue per Month - Date Spine (red = months with no order data)')
ax.set_ylabel('Revenue (R$)')
ax.set_xlabel('')
plt.setp(ax.get_xticklabels(), rotation=45, ha='right')
from matplotlib.patches import Patch
ax.legend(handles=[Patch(color=PRIMARY, label='Has data'),
Patch(color=ACCENT, label='No data (spine fills the gap)')])
plt.tight_layout()
plt.show()
print(f'Total months in spine : {len(df_spine)}')
print(f"Months with no data : {(df_spine['status'] == 'missing').sum()}")
Total months in spine : 26 Months with no data : 2
Technique 5 - window function for purchase gap analysis¶
lag() is one of the window functions I reach for most. It pulls the value from the previous row within a partition. Here, partitioning by customer_unique_id gives each customer their own ordered run of purchases, and lag grabs the timestamp of their previous order. The result is the gap in days between consecutive orders, which you can then bucket into churn-risk tiers.
The join to customer_unique_id matters here for the same reason it did in section 5. Partitioning by customer_id would turn up zero repeat purchases, because every order gets a fresh id in this dataset.
Out of the full order history, only 3,345 orders turn out to be repeat purchases, the ~3% of customers who come back at all, and that small group still has a wide spread of time between purchases. The average gap tells you less than the full shape does. Someone who normally buys every 30 days and hasn't bought in 90 is a different problem from someone with no pattern at all. Combining this with the At Risk segment from section 9 would let you separate who's genuinely overdue from who's just naturally slow. The postgres window function tutorial is a clear starting point if window functions are new to you.
#! must join to customers to get customer_unique_id, the real person id.
#! partitioning by customer_id (orders) finds no repeat purchases since
#! each order gets a fresh customer_id in the olist dataset.
df_gaps = q("""
with order_gaps as (
select
c.customer_unique_id,
o.order_id,
o.order_purchase_timestamp,
lag(o.order_purchase_timestamp) over (
partition by c.customer_unique_id
order by o.order_purchase_timestamp
) as prev_order_ts
from orders o
join customers c on c.customer_id = o.customer_id
)
select
customer_unique_id,
order_id,
order_purchase_timestamp::date as order_date,
round(
extract(epoch from (
order_purchase_timestamp - prev_order_ts
)) / 86400, 0
) as days_since_last_order,
case
when extract(epoch from (order_purchase_timestamp - prev_order_ts)) / 86400 > 180 then 'High risk (6+ months)'
when extract(epoch from (order_purchase_timestamp - prev_order_ts)) / 86400 > 90 then 'Watch (3-6 months)'
when extract(epoch from (order_purchase_timestamp - prev_order_ts)) / 86400 > 30 then 'Normal (1-3 months)'
else 'Frequent (<30 days)'
end as gap_label
from order_gaps
where prev_order_ts is not null
""")
gap_summary = (df_gaps['gap_label']
.value_counts()
.sort_index()
.reset_index())
gap_summary.columns = ['gap_label', 'count']
color_map = {
'Frequent (<30 days)': PRIMARY,
'Normal (1-3 months)': MUTED,
'Watch (3-6 months)': "#FF9A03",
'High risk (6+ months)': ACCENT,
}
fig, ax = plt.subplots(figsize=(10, 4))
sns.barplot(data=gap_summary, x='count', y='gap_label', hue='gap_label',
palette=color_map, ax=ax, legend=False)
for i, v in enumerate(gap_summary['count']):
ax.text(v, i, f' {v:,.0f}', va='center', fontsize=10)
ax.set_title('Repeat Orders by Purchase Gap (churn risk segmentation)')
ax.set_xlabel('Number of Repeat Orders')
ax.set_ylabel('')
ax.xaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'{x:,.0f}'))
ax.set_xlim(0, gap_summary['count'].max() * 1.15)
plt.tight_layout()
plt.show()
print(f'Total repeat orders analysed: {len(df_gaps):,}')
Total repeat orders analysed: 3,345
This is one of the most reusable patterns in analytics. Partition by entity, order by time, and use lag to compute the gap. The same structure works for revenue gaps, session gaps, or any "time between events" question you can think of.
Technique 6 - json aggregation for api-ready output¶
Postgres's json functions don't come up as often as the others in this project, but they're genuinely handy. json_build_object and json_agg let you shape a query result straight into nested json with no transformation needed on the application side.
The part I found most interesting is the correlated subquery inside json_build_object that fetches each seller's top 3 product categories. It runs once per outer row, nested inside an aggregate function, and what comes out is a self-contained json profile per seller, the kind of thing a backend api would normally stitch together from several queries in application code. The json function docs list the full set, which is larger than I expected.
df_json = q("""
select
s.seller_id,
s.seller_state,
json_build_object(
'seller_id', s.seller_id,
'state', s.seller_state,
'total_orders', count(distinct oi.order_id),
'total_revenue', round(sum(oi.price)::numeric, 2),
'avg_delivery_days', round(avg(
extract(epoch from (
o.order_delivered_customer_date
- o.order_purchase_timestamp
)) / 86400
)::numeric, 1),
'avg_review_score', round(avg(rev.review_score)::numeric, 2),
'top_categories', (
select json_agg(cat order by cnt desc)
from (
select
p.product_category_name as cat,
count(*) as cnt
from order_items oi2
join products p on p.product_id = oi2.product_id
where oi2.seller_id = s.seller_id
group by p.product_category_name
order by cnt desc
limit 3
) cats
)
) as seller_profile
from sellers s
join order_items oi on oi.seller_id = s.seller_id
join orders o on o.order_id = oi.order_id
left join order_reviews rev on rev.order_id = o.order_id
where o.order_delivered_customer_date is not null
group by s.seller_id, s.seller_state
having count(distinct oi.order_id) >= 50
order by count(distinct oi.order_id) desc
limit 5
""")
# Pretty-print the JSON profiles
import json as json_lib
for _, row in df_json.iterrows():
profile = row['seller_profile']
if isinstance(profile, str):
profile = json_lib.loads(profile)
print(json_lib.dumps(profile, indent=2))
print()
{
"seller_id": "6560211a19b47992c3666cc44a7e94c0",
"state": "SP",
"total_orders": 1819,
"total_revenue": 120983.82,
"avg_delivery_days": 9.5,
"avg_review_score": 3.95,
"top_categories": [
"relogios_presentes",
"fashion_bolsas_e_acessorios",
"audio"
]
}
{
"seller_id": "4a3ca9315b744ce9f8e9374361493884",
"state": "SP",
"total_orders": 1772,
"total_revenue": 199408.32,
"avg_delivery_days": 14.4,
"avg_review_score": 3.83,
"top_categories": [
"cama_mesa_banho",
"casa_conforto",
"moveis_decoracao"
]
}
{
"seller_id": "cc419e0650a3c5ba77189a1882b7556a",
"state": "SP",
"total_orders": 1651,
"total_revenue": 103152.56,
"avg_delivery_days": 11.6,
"avg_review_score": 4.15,
"top_categories": [
"beleza_saude",
"perfumaria",
"telefonia"
]
}
{
"seller_id": "1f50f920176fa81dab994f9023523100",
"state": "SP",
"total_orders": 1399,
"total_revenue": 107147.91,
"avg_delivery_days": 15.6,
"avg_review_score": 3.99,
"top_categories": [
"ferramentas_jardim",
"bebes",
"sinalizacao_e_seguranca"
]
}
{
"seller_id": "da8622b14eb17ae2831f4ac5b9dab84a",
"state": "SP",
"total_orders": 1311,
"total_revenue": 162303.67,
"avg_delivery_days": 11.2,
"avg_review_score": 4.08,
"top_categories": [
"cama_mesa_banho",
"beleza_saude",
"cool_stuff"
]
}
Technique 7 - distinct on for the latest row per group¶
distinct on came up while I was hunting for a cleaner alternative to the row_number() subquery pattern for grabbing the latest row per group.
It's postgres-specific. distinct on (column) (link) keeps the first row for each distinct value of that column, where "first" is decided by the order by. For top-1-per-group queries it's noticeably more concise than the alternative.
The one constraint is that order by has to start with the distinct on columns, so you can't re-sort the final output by some other column directly. The cte here is how I get around that. distinct on runs first to pull the latest order per seller, and the outer query re-sorts those by date afterwards.
For comparison, here's the row_number() version that works on any database.
select * from (
select
oi.seller_id,
o.order_id,
o.order_purchase_timestamp::date as latest_order_date,
o.order_status,
row_number() over (
partition by oi.seller_id
order by o.order_purchase_timestamp desc
) as rn
from order_items oi
join orders o on o.order_id = oi.order_id
) t
where rn = 1
df_distinct = q("""
with latest_per_seller as (
select distinct on (oi.seller_id)
oi.seller_id,
o.order_id,
o.order_purchase_timestamp::date as latest_order_date,
o.order_status
from order_items oi
join orders o on o.order_id = oi.order_id
order by oi.seller_id, o.order_purchase_timestamp desc
)
select *
from latest_per_seller
order by latest_order_date desc
limit 10
""")
print(f'rows returned: {len(df_distinct)} (one per seller — most recent order only)')
df_distinct
rows returned: 10 (one per seller — most recent order only)
| seller_id | order_id | latest_order_date | order_status | |
|---|---|---|---|---|
| 0 | 25be943a321c8938947bdaabca979a90 | 54282e97f61c23b78330c15b154c867d | 2018-09-03 | shipped |
| 1 | 7d7866a99a8656a42c7ff6352a433410 | 03ef5dedbe7492bdae72eec50764c43f | 2018-08-29 | delivered |
| 2 | 0be8ff43f22e456b4e0371b2245e4d01 | d70442bc5e3cb7438da497cc6a210f80 | 2018-08-29 | delivered |
| 3 | 6560211a19b47992c3666cc44a7e94c0 | fb393211459aac00af932cd7ab4fa2cc | 2018-08-29 | delivered |
| 4 | 2a84855fd20af891be03bc5924d2b453 | 168626408cb32af0ffaf76711caae1dc | 2018-08-29 | delivered |
| 5 | 6b90f847357d8981edd79a1eb1bf0acb | d03ca98f59480e7e76c71fa83ecd8fb6 | 2018-08-29 | delivered |
| 6 | 5a413ade68e8f8d93071a7f52a64cb9e | c84d88553f9878bf2c7ecda2eb211ece | 2018-08-29 | delivered |
| 7 | 67bf6941ba2f1fa1d02c375766bc3e53 | 0b223d92c27432930dfe407c6aea3041 | 2018-08-29 | delivered |
| 8 | 5f2684dab12e59f83bef73ae57724e45 | 52018484704db3661b98ce838612b507 | 2018-08-29 | delivered |
| 9 | 9f505651f4a6abe901a56cdc21508025 | 35a972d7f8436f405b56e36add1a7140 | 2018-08-29 | delivered |
What's Next¶
A geographic choropleth is the visualisation the state revenue chart always felt like it wanted to be. Plotting order density on an actual map of Brazil's states would make the São Paulo concentration obvious at a glance, without anyone having to read a bar chart. Geopandas has the shapefiles, and the state revenue query already returns everything needed, so it's mainly a matter of wiring the two together.
The marketing leads table sat in the dataset the whole time and never got touched. It has acquisition channel data that would let you connect customer segments back to where those customers actually came from. The 3% repeat rate looks like a product problem right now. It might turn out to be a channel problem once acquisition source is in the picture.
Confidence intervals on the delivery-to-review correlation would make that finding more rigorous. Right now it's a clean-looking pattern across five delivery buckets, but I'd want bootstrap intervals before citing a specific number. The direction isn't in doubt. The magnitude is the part that still needs verifying.
Seller-level cancellation and refund rates would round out the seller performance picture. The current analysis only covers delivered orders, which quietly filters out the sellers whose main problem is never getting to delivery at all. A seller with a 20% cancellation rate and excellent delivery stats is net worse than one with no cancellations and delivery stats that are merely good.