v0.6.0

A minor bump, and the rule at the top of this file is why. The ClickBench planner route stops charging firepanda for reading its grammar tables on every timed query, which takes one to one and a half milliseconds off each query through that route. It is a fixed amount a query, so it moves the fast half of the planner comparison and not the slow half, and a planner number written before this is not comparable with one written after it. The engine table is the hand written route and nothing in it moves. The driver needs firepanda 0.8.26 or later.

The ClickBench planner route stops paying for firepanda's grammar tables on every query

The planner route called firepanda.sql.run, which built the grammar, the jump table built from it and the function catalog on every statement and threw them away. Those three are read out of generated tables, do not depend on the statement, the catalog or the data, and are never written to after they are built. The driver now builds one Dialect for the process, next to the catalog registration that was already outside the clock, and run_clickbench_sql takes it. Parsing, binding, optimizing and lowering are still timed, and the suite README writes down the test for which side of the clock something falls on.

This is what every other engine here already gets. A DuckDB connection carries its parser tables and its function registry, the suite opens it in load and times connection.execute, and pandas and Polars parse no SQL here at all.

Two drivers built from the same source with and without the change, run back to back, six interleaved rounds of five runs a query: the paired median saving was 1.15 ms on q21, 1.15 on q24, 1.55 on q30, 1.05 on q38 and 1.51 on q42, and 29 of the 30 pairs went the same way. A full planner pass agrees on all 43 answers. It is a fixed amount a query, so it moves the fast half of the suite and not the slow half, and planner numbers from before and after this are not comparable. That is why the release carrying it is a minor one.

The driver needs firepanda 0.8.26 or later, which is the release that added Dialect. Closes #108.

v0.5.5

A patch release. No engine number moves, no published table changes and the measurement is the same measurement. What changes is that the planner comparison now has two more passes under it, taken with everything that has landed since the table was, and that the last gap the q23 paragraph said was open has a price on it.

The planner pair is taken twice more, and the ClickBench suite README says what the two passes agree on

Two passes of pixi run clickbench-planner --size 1M --runs 5 on 23 September, an hour apart, both agreeing with the hand written route on all 43 answers. The first came to 1136.4 ms by hand against 1585.9 through the planner for a ratio of 1.40, with the load average between 5 and 8. The second came to 2029.7 against 2795.3 for 1.38, with the load between 9 and 12. The table is not replaced with either, because the hand written side alone reads 1136.4 against the 732.6 the table was taken at and a reader comparing a row against its old value would be reading the laptop.

Individual rows from those passes are not worth quoting and the paragraph says so: q16 read 1.20 and then 2.25, q29 6.05 and then 3.89, q23 1.61 and then 2.11. What held still is the split by how long the hand written route takes. The queries it answers in under ten milliseconds came to 2.33 and then 2.24, with a median of 2.22 ms and then 2.97 ms added per query. The ones it takes ten milliseconds or more over came to 1.30 and then 1.34, down from the 1.46 the section already carried and the 2.12 before that. The part that does not scale is the front end being paid for, which is now tamnd/firepanda#999.

The q23 paragraph in the ClickBench suite README carries the price of what was left

It ended by saying the remaining gap was ninety five rows written out across 105 columns before ten of them are kept, with tamnd/firepanda#682 open for it. SELECT EventTime FROM hits LIMIT 10 is 2.9 ms with nothing under it, SELECT * FROM hits LIMIT 10 is 6.4, so the width costs 3.5 ms on its own and the hand written route pays that too. With the filter and the sort under it the width costs 5.1 ms, so the middle frame is 1.6 ms at most, on the one query in the suite with this shape. #682 is closed on the measurement rather than on the rule it was opened for.

v0.5.4

A patch release. No engine number moves, no published table changes and the measurement is the same measurement. What changes is that the two worst rows of the planner comparison now say what was done about them, which is the part a reader cannot get from the numbers.

The seven date range queries in the ClickBench suite README have their fixes named

That paragraph said the seven were the worst block in the table, 83.9 ms by hand against 276.2, and that the cause was each conjunct becoming a filter of its own and copying the rows that survived it. The diagnosis held up and two changes landed against it. tamnd/firepanda#962 hands the chunk straight back when a comparison kept every row, which is what both date comparisons do here, since the 1M partition lies entirely inside the month the queries ask for. tamnd/firepanda#970 tells a filter with another filter above it to write a selection whatever share it keeps, since the copy the threshold asks for is one the next filter makes again.

The paragraph now carries the paired measurement, two drivers built from the same source here and run back to back on a quiet machine, CPU per run in milliseconds: q38 59.4 against 12.6, q41 21.6 against 6.3, q40 18.4 against 11.8, q42 27.2 against 20.1, q37 80.0 against 62.8, q36 149.1 against 121.9, q39 312.1 against 290.6. It also carries a planner pass where the block read 223.7 ms through the front end against the 276.2 in the table, on a pass whose hand written side for those seven came to 80.3 against 83.9, which is close enough to compare across. No number in the table changes in this release, and it says why: the machine has not been quiet enough to retake the pair since the changes landed, and four passes taken this morning came to hand written totals between 861 and 2498.

The q29 paragraph in the ClickBench suite README names what was actually wrong

v0.5.3 left q29 as the worst single row at 6.01 and pointed at tamnd/firepanda#922, which was open and did not yet have a cause. It has one now, tamnd/firepanda#929: a reduction applied an aggregate's folded operation once per state slot rather than once per column, and a sum marked to answer null over a column that held nothing owns two slots, which is what the SQL front end builds for every SUM. Both slots read the same column under the same constant, so ninety marked sums meant ninety extra passes down the million rows.

The paragraph now carries the paired measurement, two drivers built from the same source here and run back to back over thirteen rounds, 583 ms of CPU time per run against 380. It also carries the quiet machine wall reading, 35.4 ms against the 43.2 still printed in the table above it, and says plainly that the table was taken before the fix landed and that the row will move when the pair is next taken on a machine quiet enough to publish from. No number in the table changes in this release.

v0.5.3

A patch release. No engine number moves and the measurement does not change. What changes is the planner pair, which was retaken after the regression v0.5.2 blamed it on was fixed, and which now reads 1.61 where it read 2.25.

The planner pair is measured again, after the morsel regression was fixed

v0.5.2 published a ratio of 2.25 and said in the same breath that most of it was tamnd/firepanda#803 rather than anything to do with planning. That is now fixed by tamnd/firepanda#921, which moves the cut from the scan's constructor to the point where the pipeline knows what is above it, so a line whose first operator is a group by or a reduction is no longer handed morsels it has nothing to spread over.

The pair reads 732.6 ms by hand against 1181.0 through the planner, a factor of 1.61, at 1M with three runs per query per route. The hand written totals either side of the fix are 671.0 and 732.6, so the drop in the planner total from 1507.1 is the route and not the laptop. The five group by rows moved together: q33 from 161.1 ms to 51.2, q32 from 193.3 to 39.7, q16 from 63.2 to 30.9, q29 from 110.8 to 68.2, each pair built from the same source here and run back to back.

The section in suites/clickbench/README.md is rewritten around the new numbers. What it points at now is the seven queries that filter a date range before they group, 83.9 ms by hand against 276.2, a factor of 3.29, where each conjunct becomes a filter of its own and copies the rows that survived it. That is tamnd/firepanda#521 and it is the largest thing left in the comparison. q29 is the worst single row at 6.01 and did not come all the way back with the morsel fix, which is tamnd/firepanda#922.

The same pair was taken three times within the hour at load averages of 16, 8 and 3, and came to 1.81, 1.66 and 1.61. Both sides slow down on a busy machine and the planner route slows down more, so the README now says to read the ratio and not the milliseconds, with the measurement behind it.

A hand written route timed at one microsecond no longer gets a ratio

v0.5.2 stopped tools/clickbench_planner.py dividing by a route that took exactly no time. q0 is the only query like that, SELECT COUNT(*) FROM hits answered out of the row count, and on a later day the driver timed it at one microsecond rather than at zero, which slipped past the guard and printed 3440.00 in the ratio column. That was the largest number in the table by a factor of a hundred and it measured the clock.

The guard is now a floor of ten microseconds rather than a test against zero. It sits an order of magnitude away from both sides of the gap it has to fall in: q0 is timed at one microsecond and the fastest query here that does read a column is q6, a minimum and a maximum over one date column, at ninety four. The row still carries both of its times and the ratio column carries a dash.

v0.5.2

A patch release. No engine number moves and the measurement does not change. What changes is the planner pair, which now covers all 43 queries instead of 38 and which says something about firepanda it did not say before.

The planner pair is measured again, and all 43 queries now run both ways

pixi run clickbench-planner --size 1M has not been run since 12 September and a lot has landed underneath it. All 43 queries now run as the hand written port and as the published SQL text through firepanda's own front end, where 38 did before, and the two routes agree on all 43 on the row count, the sums and the text hashes. Nothing in this suite is refused by either route any more. The five that were still refused went in one at a time: q27 with tamnd/firepanda#679, q28 and q39 with tamnd/firepanda#681, and q18 and q42 with EXTRACT and DATE_TRUNC under tamnd/firepanda#304.

The hand written total is 671.0 ms and the planner total is 1507.1 ms, a factor of 2.25, at 1M on the M4 laptop with five runs per query per route. The 1.87 this table carried before was over 38 queries rather than 43 and the two should not be subtracted, because both sides moved and neither moved for a reason to do with planning.

The hand written side got faster when #89 here handed every comparison in a filter over at once. The planner side got slower on 15 September when tamnd/firepanda#803 had a scan cut a tall chunk into morsels. That change was measured on a filtering line and made it two and a half times faster, and every query in this suite with a group by or a reduction under it went the other way by between one and a half and four times. q33 through the planner bisects to that commit, 53 milliseconds before it and 216 after, with the two drivers built from the same source here and run back to back. That is tamnd/firepanda#918.

No published engine number moves. The ClickBench table compares the hand written port against pandas, Polars and DuckDB and that route does not build a pipeline.

q23 is the row that moved the other way, from 10.39 to 1.73, which is tamnd/firepanda#912 for the limit that was taking the query off the cores and #95 here for the answer the driver could not read back.

The pair divided by a hand written route that answers in no time

tools/clickbench_planner.py measured all 43 queries both ways and then raised ZeroDivisionError on its way to printing the table, which is four minutes of measuring lost and nothing written to the file --json names.

q0 is SELECT COUNT(*) FROM hits and the hand written port answers it out of the row count without reading a column, which the driver times at zero once the first run has warmed it. A ratio against that is not a large number, it is not a number, so the column carries a dash and the query keeps its row with both times in it. The file --json names is now written before the table is printed, so a run that took the numbers and then fell over on the way to the screen still has them. Issue #97.

q23 through the SQL route exited on the digest instead of answering

tools/clickbench_planner.py reported q23 as a refusal on the planner side and left it out of both totals, which made the pair the planner comparison exists to measure miss the one query in the suite that asks for the table rather than for a reduction of it. The query was running and producing the right ten rows. What raised was the driver reading them back.

A frame's columns are chunked and DataFrame.__getitem__ borrows a column only when there is exactly one chunk to borrow. A pipeline hands its sink one chunk for every chunk that reached it, so an answer arrives in as many pieces as the operators left it in, and for a reduction that is always one piece. q23 filters a million rows down to ninety five, sorts them and keeps ten, and those ten come off two or three of the chunks the filter left. So whether the driver could read its own answer was decided by where the surviving rows happened to fall.

The answer is now stacked into one chunk a column before anything reads it, after the clock and the memory readings, so no published number moves. Issue #95.

v0.5.1

A patch release. The measurement does not change and no published number moves because of anything in here. What changes is that the ClickBench block on the suite README and the ClickBench row on the front page exist, and that they come from the run they should.

The first ClickBench numbers are published

All 43 queries, four engines, the 1M partition in memory mode, five runs each, and every engine agreed with every other on all 43. firepanda 0.8.13 is 9.37x pandas on the geometric mean of the times and 0.48x pandas on peak memory, polars is 4.72x and 0.61x, DuckDB is 3.18x and 1.02x. Both halves of that are in the table because both halves are the claim.

It is a laptop run rather than one from either benchmark machine, which is why the machine is now named in the second column of the front page table beside the size and the io mode. That table already carried a TPC-H row from the desktop and the paragraph under it says nothing is comparable across a machine, so the column had to say which one.

The query this suite was waiting on is q28, which groups by a regular expression replacement over a column of URLs. v0.5.0 said the number was not good and that a published table would say so once a run was folded in. It is better than it was: firepanda answers q28 at 1M in 0.82 s against DuckDB's 0.20 s in the same run, where before the regular expression work in tamnd/firepanda#863 it was 2.33 s, and the one run on a loaded laptop quoted in v0.5.0 was 7.1 s.

A probe file with no date in its name outranked every real run

pick_runs in tools/suite_readme.py takes the newest run per machine, size and io mode, and it reads the date off the front of the file name. A name not in that shape fell back to the file stem, so probe-rivals.json compared as probe-rivals, and p sorts above every digit there is. Nine probe files from an afternoon in September therefore beat every dated file beside them, and a ClickBench block generated locally came out holding the two engines somebody had been probing.

An undated file now loses to any dated one, and is still used when it is the only file for that machine, size and io mode, since a probe is a real run of something and a labelled table beats an empty one. Nothing published was ever wrong, because the block had never been folded in at all and the scheduled machines carry no probe files. Issue #92.

v0.5.0

A minor bump rather than a patch, because published numbers move on both suites this covers.

firepanda's ClickBench coverage goes from 42 of 43 to 43 of 43, so its geometric mean and its coverage line in that table are computed over a different set of queries than before. And six TPC-H queries got faster because the firepanda ports stopped carrying columns nobody asked for through joins, so any TPC-H result file written before this is not comparable with one written after it on those six. No other engine is touched by any of it.

firepanda answers all 43 ClickBench queries

q28 groups by a regular expression replacement over the referer, and firepanda had no regular expression engine to run it on, so the driver raised a refusal with the reason on it. firepanda 0.8.6 has one, so the port compiles the published pattern once for the column and runs it, and the refusal list is now empty.

It does not call text_hostname, which is that same pattern written out by hand in Mojo and is faster on it, because answering a benchmark query with a kernel written for that one query measures the kernel and not the engine. Checked against DuckDB 1.5.5 on the 1M partition: two rows, four columns, the same average to every digit, the same count, and the same digest on both text columns.

The number is not good. One run on a loaded laptop put firepanda at 7.1 s against DuckDB's 0.35 s on that query, and it is tracked as tamnd/firepanda#830.

The firepanda TPC-H filters hand all their comparisons over at once

A filter with several comparisons used to and them together two at a time, and every one of those pairwise calls writes a whole mask column for the next call to read straight back. The queries now hand the whole list to one pass. q6 goes 10.3 ms to 8.3 ms, q12 18.1 to 16.6, and q16 and q19 are inside the noise this run could measure.

TPC-H at SF10, all four engines, and the memory ceiling it found

All four answer all twenty two at sixty million line items. firepanda is first on the suite total, 5.18 s against Polars' 7.06, DuckDB's 10.17 and pandas' 98.25, on the least CPU of the three fast engines. It also peaked at 31.57 GB on a machine with 31 GB of RAM, where no other engine went above 25.45, so it was the only one paging and its per query numbers at that size are an upper bound. The cause is that firepanda reads Parquet by handing the file to DuckDB and so holds two copies of the table, which a native reader fixes and no query rewrite will.

The firepanda TPC-H queries project before they join, and take a top n instead of sorting

Five queries were carrying columns through joins that nothing downstream read, and q10 was sorting thirty seven thousand rows to keep twenty. The pandas ports already did this by hand and Polars gets it from its optimizer, so the old code was measuring firepanda doing more work than either.

v0.4.5

A patch. Nothing in the harness changed and no published engine number moves.

The one thing in it is the planner pair on ClickBench, measured again because tamnd/firepanda#680 landed and five more of the 43 go through the planner than did when the table was published. 38 of the 43 run both ways now, the two routes agree on all 38, and the factor is 1.87 against the 1.67 that was there. The five that were added are the whole of that move: they come to 2.9 where the other 33 come to 1.70, and the reason is that each conjunct becomes a physical filter that copies its survivors, so six predicates means six gathers where the hand written port builds one mask and gathers once. That is tamnd/firepanda#521 rather than anything the planner decided.

v0.4.4

A patch. No published number changes and there is one new measurement, which is what firepanda's own planner costs against the same 43 queries written by hand.

pixi run clickbench-planner --size 1M runs all 43 twice in the firepanda driver over the same loaded table, once as the hand written port and once as the published SQL text through firepanda's front end, with parsing and planning inside the timed region. 33 of the 43 run both ways at 1M, the two routes agree on all 33, and the totals are 326.4 ms by hand against 543.9 ms planned, a factor of 1.67. TPC-H measured the same pair at 4.8, on a suite that has joins to order.

The pair was taken twice, hours apart on the same laptop, before and after the LIKE kernel landed in firepanda. Over the 29 queries both passes ran, each side fell by about 22 percent and the ratio moved from 1.615 to 1.617, so the ratio is what this measurement produces and the milliseconds are the machine's mood.

Full notes in CHANGELOG.md.

v0.4.3

A patch. No published number changes and there is one new measurement, which is what a group by costs in memory when its answer is nearly as large as the table it read.

What a group by adds on top of the load is measured too

pixi run group-memory --size 1M is the sibling of load-memory. It loads the hits table, reads the peak resident set, runs q32 once and reads the peak again, one process per engine, and reports the difference. q32 groups by WatchID and ClientIP with no filter, so the answer has close to one row per row of input, and that is the one shape in the suite where a group by is an allocation question rather than a throughput one.

The difference between the two readings is the only part of a process peak that can be attributed to a query. A peak never falls, so comparing the raw peaks would mostly compare the loaders, which load-memory already does on purpose.

At 1M the group by adds 0.15 GB for pandas and 0.11 GB for duckdb, and nothing measurable for firepanda and polars. Those two zeros are bounds rather than measurements, because both engines' loads reached higher than anything their query did, and that is said in the table rather than left to be read as a group by that costs nothing. What a zero does rule out is a second copy of the table, which would have been a gigabyte and would have shown. The size that would settle it is 10M, and it does not fit on the laptop these were taken on, so it waits on tamnd/firepanda#427 or on the bench machines.

The driver now prints peak_rss_after_load_bytes next to the peak it already printed, which is where the first of the two readings comes from. It costs nothing, since the driver was already reading the process facts at that point and reporting only the current resident set out of them, which is zero on a platform with no /proc.