Lecture Notes for cs581

Example lineitem data

1
2


echo "l_orderkey|l_partkey|l_suppkey|l_linenumber|l_quantity|l_extendedprice|l_discount|l_tax|l_returnflag|l_linestatus|l_shipdate|l_commitdate|l_receiptdate|l_shipinstruct|l_shipmode|l_comment|"
head -n 20 ../../../dbs/lineitem.cpy

l_orderkey	l_partkey	l_suppkey	l_linenumber	l_quantity	l_extendedprice	l_discount	l_tax	l_returnflag	l_linestatus	l_shipdate	l_commitdate	l_receiptdate	l_shipinstruct	l_shipmode	l_comment
1	155190	7706	1	17	21168.23	0.04	0.02	N	O	1996-03-13	1996-02-12	1996-03-22	DELIVER	IN	PERSON	TRUCK	egular	courts	above	the
1	67310	7311	2	36	45983.16	0.09	0.06	N	O	1996-04-12	1996-02-28	1996-04-20	TAKE	BACK	RETURN	MAIL	ly	final	dependencies:	slyly	bold
1	63700	3701	3	8	13309.6	0.1	0.02	N	O	1996-01-29	1996-03-05	1996-01-31	TAKE	BACK	RETURN	REG	AIR	riously.	regular,	express	dep
1	2132	4633	4	28	28955.64	0.09	0.06	N	O	1996-04-21	1996-03-30	1996-05-16	NONE	AIR	lites.	fluffily	even	de
1	24027	1534	5	24	22824.48	0.1	0.04	N	O	1996-03-30	1996-03-14	1996-04-01	NONE	FOB		pending	foxes.	slyly	re
1	15635	638	6	32	49620.16	0.07	0.02	N	O	1996-01-30	1996-02-07	1996-02-03	DELIVER	IN	PERSON	MAIL	arefully	slyly	ex
2	106170	1191	1	38	44694.46	0.0	0.05	N	O	1997-01-28	1997-01-14	1997-02-02	TAKE	BACK	RETURN	RAIL	ven	requests.	deposits	breach	a
3	4297	1798	1	45	54058.05	0.06	0.0	R	F	1994-02-02	1994-01-04	1994-02-23	NONE	AIR	ongside	of	the	furiously	brave	acco
3	19036	6540	2	49	46796.47	0.1	0.0	R	F	1993-11-09	1993-12-20	1993-11-24	TAKE	BACK	RETURN	RAIL		unusual	accounts.	eve
3	128449	3474	3	27	39890.88	0.06	0.07	A	F	1994-01-16	1993-11-22	1994-01-23	DELIVER	IN	PERSON	SHIP	nal	foxes	wake.
3	29380	1883	4	2	2618.76	0.01	0.06	A	F	1993-12-04	1994-01-07	1994-01-01	NONE	TRUCK	y.	fluffily	pending	d
3	183095	650	5	28	32986.52	0.04	0.0	R	F	1993-12-14	1994-01-10	1994-01-01	TAKE	BACK	RETURN	FOB	ages	nag	slyly	pending
3	62143	9662	6	26	28733.64	0.1	0.02	A	F	1993-10-29	1993-12-18	1993-11-04	TAKE	BACK	RETURN	RAIL	ges	sleep	after	the	caref
4	88035	5560	1	30	30690.9	0.03	0.08	N	O	1996-01-10	1995-12-14	1996-01-18	DELIVER	IN	PERSON	REG	AIR	-	quickly	regular	packages	sleep.	idly
5	108570	8571	1	15	23678.55	0.02	0.04	R	F	1994-10-31	1994-08-31	1994-11-20	NONE	AIR	ts	wake	furiously
5	123927	3928	2	26	50723.92	0.07	0.08	R	F	1994-10-16	1994-09-25	1994-10-19	NONE	FOB	sts	use	slyly	quickly	special	instruc
5	37531	35	3	50	73426.5	0.08	0.03	A	F	1994-08-08	1994-10-13	1994-08-26	DELIVER	IN	PERSON	AIR	eodolites.	fluffily	unusual
6	139636	2150	1	37	61998.31	0.08	0.03	A	F	1992-04-27	1992-05-15	1992-05-02	TAKE	BACK	RETURN	TRUCK	p	furiously	special	foxes
7	182052	9607	1	12	13608.6	0.07	0.03	N	O	1996-05-07	1996-03-13	1996-06-03	TAKE	BACK	RETURN	FOB	ss	pinto	beans	wake	against	th
7	145243	7758	2	9	11594.16	0.08	0.08	N	O	1996-02-01	1996-03-02	1996-02-19	TAKE	BACK	RETURN	SHIP	es.	instructions

Queries

Let's count the number of lineitems for each quantity that occurs in the table
Let's find pairs of lineitems for the same order that differ more than 20 in their quantity

1
2
3


SELECT count(*), l_quantity
FROM linetitem
GROUP BY l_quantity;

1
2
3
4


SELECT *
FROM lineitem l1, lineitem l2
WHERE l1.l_orderkey = l2.l_orderkey
      AND abs(l1.l_quantity - l2.l_quantity) > 20

Using python

Counting lineitems per quantity

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24


import csv
import time

TABLE = '../../../dbs/lineitem.cpy'
start_time = time.perf_counter()

with open(TABLE, mode='r', encoding='utf-8') as f:
    reader = csv.reader(f,delimiter='|')
    cnts = {}

    # Loop through rows
    for row in reader:
        quantity = row[4] # l_quantity is 5th column
        if quantity in cnts:
            cnts[quantity] += 1
        else:
            cnts[quantity] = 1

    for grp in cnts:
        print(f"group: {grp} with cnt: {cnts[grp]}")

end_time = time.perf_counter()
t = end_time - start_time
print(f"this took: {t:.5f} seconds to complete")

group: 17 with cnt: 120086 group: 36 with cnt: 120372 group: 8 with cnt: 120153 group: 28 with cnt: 120307 group: 24 with cnt: 119971 group: 32 with cnt: 120630 group: 38 with cnt: 120221 group: 45 with cnt: 119638 group: 49 with cnt: 119624 group: 27 with cnt: 120013 group: 2 with cnt: 119460 group: 26 with cnt: 119702 group: 30 with cnt: 119671 group: 15 with cnt: 120324 group: 50 with cnt: 119846 group: 37 with cnt: 120070 group: 12 with cnt: 119913 group: 9 with cnt: 120503 group: 46 with cnt: 120179 group: 35 with cnt: 120753 group: 5 with cnt: 119509 group: 4 with cnt: 119621 group: 44 with cnt: 120024 group: 6 with cnt: 119432 group: 31 with cnt: 119963 group: 41 with cnt: 120447 group: 13 with cnt: 119757 group: 22 with cnt: 119887 group: 34 with cnt: 119966 group: 7 with cnt: 120114 group: 25 with cnt: 120635 group: 42 with cnt: 120372 group: 40 with cnt: 119977 group: 39 with cnt: 119851 group: 43 with cnt: 119886 group: 21 with cnt: 119926 group: 23 with cnt: 120593 group: 29 with cnt: 119907 group: 3 with cnt: 120047 group: 20 with cnt: 120114 group: 48 with cnt: 120191 group: 1 with cnt: 120401 group: 11 with cnt: 119552 group: 19 with cnt: 120363 group: 33 with cnt: 120127 group: 14 with cnt: 119420 group: 10 with cnt: 119700 group: 18 with cnt: 119429 group: 47 with cnt: 120050 group: 16 with cnt: 120518 this took: 6.09617 seconds to complete

Pairs of lineitems from the same order with 20 difference in quanity

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


import csv
import time

TABLE = '../../../dbs/lineitem-6k.cpy'

start_time = time.perf_counter()

with open(TABLE, mode='r', encoding='utf-8') as f:
    reader = csv.reader(f,delimiter='|')
    results = []

    # Loop through rows
    for row1 in reader:
        orderkey = row1[0] # l_orderkey
        linenumber = row1[2] # l_linenumber
        quantity = row1[4] # l_quantity is 5th column

        # scan through file to find matching pairs
        with open(TABLE, mode='r', encoding='utf-8') as of:
            o_reader = csv.reader(of,delimiter='|')
            for row2 in o_reader:
                o_orderkey = row2[0]
                o_linenumber = row2[2] # l_linenumber
                o_quantity = row2[4] # l_quantity is 5th column

                if orderkey == o_orderkey and linenumber != o_linenumber and abs(int(quantity) - int(o_quantity)) > 20:
                    results.append(row1 + row2)

end_time = time.perf_counter()
t = end_time - start_time
print(f"this took: {t:.5f} seconds to complete with {len(results)} result rows")

this took: 33.90866 seconds to complete with 7684 result rows

this scales quadratically in the input size
so on 6M lineitems this will be about 34,000,000 seconds =~ 393 days -> longer than one year

Query CSV from a columnar database

SELECT l_quantity, count(*)
  FROM read_csv('../../../dbs/lineitem.cpy',
                header = false,
                names = ['l_orderkey', 'l_partkey', 'l_suppkey', 'l_linenumber', 'l_quantity', 'l_extendedprice', 'l_discount', 'l_tax', 'l_returnflag', 'l_linestatus', 'l_shipdate', 'l_commitdate', 'l_receiptdate', 'l_shipinstruct', 'l_shipmode', 'l_comment']
  ) lineitem
GROUP BY l_quantity

┌────────────┬──────────────┐ │ l_quantity │ count_star() │ │ int64 │ int64 │ ├────────────┼──────────────┤ │ 1 │ 120401 │ │ 8 │ 120153 │ │ 19 │ 120363 │ │ 46 │ 120179 │ │ 13 │ 119757 │ │ 12 │ 119913 │ │ 36 │ 120372 │ │ 20 │ 120114 │ │ 15 │ 120324 │ │ 22 │ 119887 │ │ 31 │ 119963 │ │ 32 │ 120630 │ │ 50 │ 119846 │ │ 33 │ 120127 │ │ 45 │ 119638 │ │ 4 │ 119621 │ │ 40 │ 119977 │ │ 23 │ 120593 │ │ 24 │ 119971 │ │ 43 │ 119886 │ │ · │ · │ │ · │ · │ │ · │ · │ │ 34 │ 119966 │ │ 47 │ 120050 │ │ 29 │ 119907 │ │ 26 │ 119702 │ │ 11 │ 119552 │ │ 16 │ 120518 │ │ 42 │ 120372 │ │ 14 │ 119420 │ │ 9 │ 120503 │ │ 17 │ 120086 │ │ 44 │ 120024 │ │ 5 │ 119509 │ │ 30 │ 119671 │ │ 39 │ 119851 │ │ 28 │ 120307 │ │ 2 │ 119460 │ │ 35 │ 120753 │ │ 37 │ 120070 │ │ 21 │ 119926 │ │ 25 │ 120635 │ └────────────┴──────────────┘ 50 rows 2 columns (40 shown)

EXPLAIN ANALYZE
SELECT l_quantity, count(*)
  FROM read_csv('../../../dbs/lineitem.cpy',
                header = false,
                names = ['l_orderkey', 'l_partkey', 'l_suppkey', 'l_linenumber', 'l_quantity', 'l_extendedprice', 'l_discount', 'l_tax', 'l_returnflag', 'l_linestatus', 'l_shipdate', 'l_commitdate', 'l_receiptdate', 'l_shipinstruct', 'l_shipmode', 'l_comment']
  ) lineitem
GROUP BY l_quantity;

┌─────────────────────────────────────┐ │┌───────────────────────────────────┐│ ││ Query Profiling Information ││ │└───────────────────────────────────┘│ └─────────────────────────────────────┘ EXPLAIN ANALYZE SELECT l_quantity, count(*) FROM read_csv('../../../dbs/lineitem.cpy', header = false, names = ['l_orderkey', 'l_partkey', 'l_suppkey', 'l_linenumber', 'l_quantity', 'l_extendedprice', 'l_discount', 'l_tax', 'l_returnflag', 'l_linestatus', 'l_shipdate', 'l_commitdate', 'l_receiptdate', 'l_shipinstruct', 'l_shipmode', 'l_comment'] ) lineitem GROUP BY l_quantity; ┌────────────────────────────────────────────────┐ │┌──────────────────────────────────────────────┐│ ││ Total Time: 0.282s ││ │└──────────────────────────────────────────────┘│ └────────────────────────────────────────────────┘ ┌───────────────────────────┐ │ QUERY │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ EXPLAIN_ANALYZE │ │ ──────────────────── │ │ │ │ 0 rows │ │ 0.00s │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ HASH_GROUP_BY │ │ ──────────────────── │ │ Groups: #0 │ │ │ │ Aggregates: │ │ count_star() │ │ │ │ │ │ │ │ 50 rows │ │ 0.05s │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ PROJECTION │ │ ──────────────────── │ │ l_quantity │ │ │ │ │ │ │ │ 6,001,215 rows │ │ 0.00s │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ TABLE_SCAN │ │ ──────────────────── │ │ Function: READ_CSV │ │ │ │ Projections: │ │ l_quantity │ │ │ │ Total Files Read: 1 │ │ │ │ Filename(s): │ │ ../../../dbs/lineitem.cpy │ │ │ │ │ │ │ │ 6,001,215 rows │ │ 2.52s │ └───────────────────────────┘

Query data using the native storage format of the database

DROP TABLE IF EXISTS lineitem;
CREATE TABLE lineitem AS SELECT * FROM read_csv(
  '../../../dbs/lineitem.cpy',
  header = false,
  names = ['l_orderkey', 'l_partkey', 'l_suppkey', 'l_linenumber', 'l_quantity', 'l_extendedprice', 'l_discount', 'l_tax', 'l_returnflag', 'l_linestatus', 'l_shipdate', 'l_commitdate', 'l_receiptdate', 'l_shipinstruct', 'l_shipmode', 'l_comment']
);

EXPLAIN ANALYZE
  SELECT l_quantity, count(*)
  FROM lineitem
  GROUP BY l_quantity;

┌─────────────────────────────────────┐ │┌───────────────────────────────────┐│ ││ Query Profiling Information ││ │└───────────────────────────────────┘│ └─────────────────────────────────────┘ EXPLAIN ANALYZE SELECT l_quantity, count(*) FROM lineitem GROUP BY l_quantity; ┌────────────────────────────────────────────────┐ │┌──────────────────────────────────────────────┐│ ││ Total Time: 0.0037s ││ │└──────────────────────────────────────────────┘│ └────────────────────────────────────────────────┘ ┌───────────────────────────┐ │ QUERY │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ EXPLAIN_ANALYZE │ │ ──────────────────── │ │ │ │ 0 rows │ │ 0.00s │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ PROJECTION │ │ ──────────────────── │ │__internal_decompress_integ│ │ ral_bigint(#0, 1) │ │ #1 │ │ │ │ │ │ │ │ 50 rows │ │ 0.00s │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ PERFECT_HASH_GROUP_BY │ │ ──────────────────── │ │ Groups: #0 │ │ │ │ Aggregates: │ │ count_star() │ │ │ │ │ │ │ │ 50 rows │ │ 0.02s │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ PROJECTION │ │ ──────────────────── │ │ l_quantity │ │ │ │ │ │ │ │ 6,001,215 rows │ │ 0.00s │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ PROJECTION │ │ ──────────────────── │ │__internal_compress_integra│ │ l_utinyint(#0, 1) │ │ │ │ │ │ │ │ 6,001,215 rows │ │ 0.00s │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ TABLE_SCAN │ │ ──────────────────── │ │ Table: │ │ memory.main.lineitem │ │ │ │ Type: Sequential Scan │ │ │ │ Projections: │ │ l_quantity │ │ │ │ │ │ │ │ 6,001,215 rows │ │ 0.01s │ └───────────────────────────┘

EXPLAIN ANALYZE
  SELECT l.*, r.*
  FROM lineitem l, lineitem r
  WHERE l.l_orderkey = r.l_orderkey
        AND l.l_linenumber <> r.l_linenumber
        AND abs(l.l_quantity - r.l_quantity) > 20;

┌─────────────────────────────────────┐ │┌───────────────────────────────────┐│ ││ Query Profiling Information ││ │└───────────────────────────────────┘│ └─────────────────────────────────────┘ EXPLAIN ANALYZE SELECT l.*, r.* FROM lineitem l, lineitem r WHERE l.l_orderkey = r.l_orderkey AND l.l_linenumber <> r.l_linenumber AND abs(l.l_quantity - r.l_quantity) > 20; ┌────────────────────────────────────────────────┐ │┌──────────────────────────────────────────────┐│ ││ Total Time: 0.192s ││ │└──────────────────────────────────────────────┘│ └────────────────────────────────────────────────┘ ┌───────────────────────────┐ │ QUERY │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ EXPLAIN_ANALYZE │ │ ──────────────────── │ │ │ │ 0 rows │ │ 0.00s │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ PROJECTION │ │ ──────────────────── │ │ l_orderkey │ │ l_partkey │ │ l_suppkey │ │ l_linenumber │ │ l_quantity │ │ l_extendedprice │ │ l_discount │ │ l_tax │ │ l_returnflag │ │ l_linestatus │ │ l_shipdate │ │ l_commitdate │ │ l_receiptdate │ │ l_shipinstruct │ │ l_shipmode │ │ … │ │ l_partkey │ │ l_suppkey │ │ l_linenumber │ │ l_quantity │ │ l_extendedprice │ │ l_discount │ │ l_tax │ │ l_returnflag │ │ l_linestatus │ │ l_shipdate │ │ l_commitdate │ │ l_receiptdate │ │ l_shipinstruct │ │ l_shipmode │ │ l_comment │ │ │ │ │ │ │ │ 8,352,676 rows │ │ 0.01s │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (abs((l_quantity - │ │ l_quantity)) > 20) │ │ │ │ │ │ │ │ 8,352,676 rows │ │ 0.13s │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #0 │ │ #1 │ │ #2 │ │ #3 │ │ #4 │ │ #5 │ │ #6 │ │ #7 │ │ #8 │ │ #9 │ │ #10 │ │ #11 │ │ #12 │ │ #13 │ │ #14 │ │ … │ │ #16 │ │__internal_decompress_integ│ │ ral_bigint(#17, 1) │ │__internal_decompress_integ│ │ ral_bigint(#18, 1) │ │__internal_decompress_integ│ │ ral_bigint(#19, 1) │ │ #20 │ │ #21 │ │ #22 │ │__internal_decompress_strin│ │ g(#23) │ │__internal_decompress_strin│ │ g(#24) │ │ #25 │ │ #26 │ │ #27 │ │ #28 │ │__internal_decompress_strin│ │ g(#29) │ │ #30 │ │ │ │ │ │ │ │ 24,011,770 rows │ │ 0.10s │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ HASH_JOIN │ │ ──────────────────── │ │ Join Type: INNER │ │ │ │ Conditions: │ │ l_orderkey = l_orderkey │ │ l_linenumber != ├──────────────┐ │ l_linenumber │ │ │ │ │ │ │ │ │ │ │ │ 24,011,770 rows │ │ │ 1.64s │ │ └─────────────┬─────────────┘ │ ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ │ TABLE_SCAN ││ PROJECTION │ │ ──────────────────── ││ ──────────────────── │ │ Table: ││ #0 │ │ memory.main.lineitem ││ #1 │ │ ││__internal_compress_integra│ │ Type: Sequential Scan ││ l_utinyint(#2, 1) │ │ ││__internal_compress_integra│ │ Projections: ││ l_uinteger(#3, 1) │ │ l_orderkey ││__internal_compress_integra│ │ l_linenumber ││ l_usmallint(#4, 1) │ │ l_quantity ││ #5 │ │ l_partkey ││ #6 │ │ l_suppkey ││ #7 │ │ l_extendedprice ││__internal_compress_string_│ │ l_discount ││ utinyint(#8) │ │ l_tax ││__internal_compress_string_│ │ l_returnflag ││ utinyint(#9) │ │ l_linestatus ││ #10 │ │ l_shipdate ││ #11 │ │ l_commitdate ││ #12 │ │ l_receiptdate ││ #13 │ │ l_shipinstruct ││__internal_compress_string_│ │ l_shipmode ││ ubigint(#14) │ │ l_comment ││ #15 │ │ ││ │ │ Dynamic Filters: ││ │ │ optional: l_orderkey>=1 ││ │ │ AND optional: l_orderkey<││ │ │ =6000000 ││ 0.01s │ │ ││ │ │ ││ │ │ ││ │ │ 6,001,215 rows ││ 6,001,215 rows │ │ 0.20s ││ (0.01s) │ └───────────────────────────┘└─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ TABLE_SCAN │ │ ──────────────────── │ │ Table: │ │ memory.main.lineitem │ │ │ │ Type: Sequential Scan │ │ │ │ Projections: │ │ l_orderkey │ │ l_linenumber │ │ l_quantity │ │ l_partkey │ │ l_suppkey │ │ l_extendedprice │ │ l_discount │ │ l_tax │ │ l_returnflag │ │ l_linestatus │ │ l_shipdate │ │ l_commitdate │ │ l_receiptdate │ │ l_shipinstruct │ │ l_shipmode │ │ l_comment │ │ │ │ │ │ │ │ 6,001,215 rows │ │ 0.21s │ └───────────────────────────┘

Calculate speedup

1
2
3
4


python_naive_csv = 34_000_000.0
duckdb = 0.183
speedup = python_naive_csv / duckdb
print(f"speedup was: {speedup:.2e}")

speedup was: 1.86e+08

100 million times faster!

cs581 - Database Management Systems - 2026 fall