4 ms·

Would be nice to see some JIT benchmarks with sample queries and the JIT speedup.

manigandham 8y ago
From citus blog: https://www.citusdata.com/blog/2018/09/11/postgresql-11-just-in-time/ https://www.citusdata.com/blog/2018/09/11/postgresql-11-just...
anarazel 8y ago
It's a bit hard what kind of benchmark to choose - you can easily get into 3-5x territory with arbitrarily expression heavy queries (say evaluating math equations in WHERE) - but whether that's realistic is another question. CREATE TABLE large(id serial not null, tz timestamptz DEFAULT clock_timestamp() NOT NULL, data_double float8 DEFAULT random() NOT NULL, data_text text DEFAULT (random()::text) NOT NULL, data_other text NOT NULL); INSERT INTO large(data_other) SELECT generate_series(1, 100000000) i; SELECT pg_prewarm('large'); yields (best of three) postgres[23419][1]=# set jit = 1; postgres[23419][1]=# EXPLAIN ANALYZE SELECT min(tz), sum(data_double), max(data_double) FROM large WHERE tz > '2018-09-23 14:47:30.298302-07'; ┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Finalize Aggregate (cost=1495625.42..1495625.43 rows=1 width=24) (actual time=3871.540..3871.540 rows=1 loops=1) │ │ -> Gather (cost=1495625.20..1495625.41 rows=2 width=24) (actual time=3871.388..3873.705 rows=3 loops=1) │ │ Workers Planned: 2 │ │ Workers Launched: 2 │ │ -> Partial Aggregate (cost=1494625.20..1494625.21 rows=1 width=24) (actual time=3856.050..3856.050 rows=1 loops=3) │ │ -> Parallel Seq Scan on large (cost=0.00..1417317.00 rows=10307760 width=16) (actual time=98.533..2216.673 rows=33333333 loops=3) │ │ Filter: (tz > '2018-09-23 14:47:30.298302-07'::timestamp with time zone) │ │ Planning Time: 0.041 ms │ │ JIT: │ │ Functions: 8 │ │ Generation Time: 0.934 ms │ │ Inlining: true │ │ Inlining Time: 5.145 ms │ │ Optimization: true │ │ Optimization Time: 55.719 ms │ │ Emission Time: 43.121 ms │ │ Execution Time: 3874.702 ms │ └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (17 rows) postgres[23419][1]=# set jit = 0; postgres[23419][1]=# EXPLAIN ANALYZE SELECT min(tz), sum(data_double), max(data_double) FROM large WHERE tz > '2018-09-23 14:47:30.298302-07'; ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Finalize Aggregate (cost=1495625.42..1495625.43 rows=1 width=24) (actual time=5214.622..5214.622 rows=1 loops=1) │ │ -> Gather (cost=1495625.20..1495625.41 rows=2 width=24) (actual time=5214.468..5216.057 rows=3 loops=1) │ │ Workers Planned: 2 │ │ Workers Launched: 2 │ │ -> Partial Aggregate (cost=1494625.20..1494625.21 rows=1 width=24) (actual time=5179.552..5179.552 rows=1 loops=3) │ │ -> Parallel Seq Scan on large (cost=0.00..1417317.00 rows=10307760 width=16) (actual time=0.012..2671.871 rows=33333333 loops=3) │ │ Filter: (tz > '2018-09-23 14:47:30.298302-07'::timestamp with time zone) │ │ Planning Time: 0.111 ms │ │ Execution Time: 5216.123 ms │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (9 rows) Now obviously those queries are somewhat nonsensical, but IMO they're not an unrealistic representation of what kind of operations happen in analytics queries. If you disable parallelism / add more aggregates / more filters, the benefit get bigger. If you add more joins etc, the benefits get smaller (because the overhead is in parts that don't benefit from JITing).
- anarazel 8y ago
  Oops, those included per-row timing overhead. Here's the same without that: postgres[24261][1]=# set jit = 1; postgres[24261][1]=# EXPLAIN (ANALYZE, TIMING OFF) SELECT min(tz), sum(data_double), max(data_double) FROM large WHERE tz > '2018-09-23 14:47:30.298302-07'; ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Finalize Aggregate (cost=1495625.42..1495625.43 rows=1 width=24) (actual rows=1 loops=1) │ │ -> Gather (cost=1495625.20..1495625.41 rows=2 width=24) (actual rows=3 loops=1) │ │ Workers Planned: 2 │ │ Workers Launched: 2 │ │ -> Partial Aggregate (cost=1494625.20..1494625.21 rows=1 width=24) (actual rows=1 loops=3) │ │ -> Parallel Seq Scan on large (cost=0.00..1417317.00 rows=10307760 width=16) (actual rows=33333333 loops=3) │ │ Filter: (tz > '2018-09-23 14:47:30.298302-07'::timestamp with time zone) │ │ Planning Time: 0.121 ms │ │ JIT: │ │ Functions: 8 │ │ Inlining: true │ │ Optimization: true │ │ Execution Time: 2440.328 ms │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (13 rows) Time: 2440.891 ms (00:02.441) postgres[24261][1]=# set jit = 0; postgres[24261][1]=# EXPLAIN (ANALYZE, TIMING OFF) SELECT min(tz), sum(data_double), max(data_double) FROM large WHERE tz > '2018-09-23 14:47:30.298302-07'; ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Finalize Aggregate (cost=1495625.42..1495625.43 rows=1 width=24) (actual rows=1 loops=1) │ │ -> Gather (cost=1495625.20..1495625.41 rows=2 width=24) (actual rows=3 loops=1) │ │ Workers Planned: 2 │ │ Workers Launched: 2 │ │ -> Partial Aggregate (cost=1494625.20..1494625.21 rows=1 width=24) (actual rows=1 loops=3) │ │ -> Parallel Seq Scan on large (cost=0.00..1417317.00 rows=10307760 width=16) (actual rows=33333333 loops=3) │ │ Filter: (tz > '2018-09-23 14:47:30.298302-07'::timestamp with time zone) │ │ Planning Time: 0.041 ms │ │ Execution Time: 4140.116 ms │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (9 rows) Time: 4140.499 ms (00:04.140)

From citus blog: https://www.citusdata.com/blog/2018/09/11/postgresql-11-just-in-time/ https://www.citusdata.com/blog/2018/09/11/postgresql-11-just...

anarazel 8y ago

It's a bit hard what kind of benchmark to choose - you can easily get into 3-5x territory with arbitrarily expression heavy queries (say evaluating math equations in WHERE) - but whether that's realistic is another question. CREATE TABLE large(id serial not null, tz timestamptz DEFAULT clock_timestamp() NOT NULL, data_double float8 DEFAULT random() NOT NULL, data_text text DEFAULT (random()::text) NOT NULL, data_other text NOT NULL); INSERT INTO large(data_other) SELECT generate_series(1, 100000000) i; SELECT pg_prewarm('large'); yields (best of three) postgres[23419][1]=# set jit = 1; postgres[23419][1]=# EXPLAIN ANALYZE SELECT min(tz), sum(data_double), max(data_double) FROM large WHERE tz > '2018-09-23 14:47:30.298302-07'; ┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Finalize Aggregate (cost=1495625.42..1495625.43 rows=1 width=24) (actual time=3871.540..3871.540 rows=1 loops=1) │ │ -> Gather (cost=1495625.20..1495625.41 rows=2 width=24) (actual time=3871.388..3873.705 rows=3 loops=1) │ │ Workers Planned: 2 │ │ Workers Launched: 2 │ │ -> Partial Aggregate (cost=1494625.20..1494625.21 rows=1 width=24) (actual time=3856.050..3856.050 rows=1 loops=3) │ │ -> Parallel Seq Scan on large (cost=0.00..1417317.00 rows=10307760 width=16) (actual time=98.533..2216.673 rows=33333333 loops=3) │ │ Filter: (tz > '2018-09-23 14:47:30.298302-07'::timestamp with time zone) │ │ Planning Time: 0.041 ms │ │ JIT: │ │ Functions: 8 │ │ Generation Time: 0.934 ms │ │ Inlining: true │ │ Inlining Time: 5.145 ms │ │ Optimization: true │ │ Optimization Time: 55.719 ms │ │ Emission Time: 43.121 ms │ │ Execution Time: 3874.702 ms │ └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (17 rows) postgres[23419][1]=# set jit = 0; postgres[23419][1]=# EXPLAIN ANALYZE SELECT min(tz), sum(data_double), max(data_double) FROM large WHERE tz > '2018-09-23 14:47:30.298302-07'; ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Finalize Aggregate (cost=1495625.42..1495625.43 rows=1 width=24) (actual time=5214.622..5214.622 rows=1 loops=1) │ │ -> Gather (cost=1495625.20..1495625.41 rows=2 width=24) (actual time=5214.468..5216.057 rows=3 loops=1) │ │ Workers Planned: 2 │ │ Workers Launched: 2 │ │ -> Partial Aggregate (cost=1494625.20..1494625.21 rows=1 width=24) (actual time=5179.552..5179.552 rows=1 loops=3) │ │ -> Parallel Seq Scan on large (cost=0.00..1417317.00 rows=10307760 width=16) (actual time=0.012..2671.871 rows=33333333 loops=3) │ │ Filter: (tz > '2018-09-23 14:47:30.298302-07'::timestamp with time zone) │ │ Planning Time: 0.111 ms │ │ Execution Time: 5216.123 ms │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (9 rows) Now obviously those queries are somewhat nonsensical, but IMO they're not an unrealistic representation of what kind of operations happen in analytics queries. If you disable parallelism / add more aggregates / more filters, the benefit get bigger. If you add more joins etc, the benefits get smaller (because the overhead is in parts that don't benefit from JITing).

anarazel 8y ago
Oops, those included per-row timing overhead. Here's the same without that: postgres[24261][1]=# set jit = 1; postgres[24261][1]=# EXPLAIN (ANALYZE, TIMING OFF) SELECT min(tz), sum(data_double), max(data_double) FROM large WHERE tz > '2018-09-23 14:47:30.298302-07'; ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Finalize Aggregate (cost=1495625.42..1495625.43 rows=1 width=24) (actual rows=1 loops=1) │ │ -> Gather (cost=1495625.20..1495625.41 rows=2 width=24) (actual rows=3 loops=1) │ │ Workers Planned: 2 │ │ Workers Launched: 2 │ │ -> Partial Aggregate (cost=1494625.20..1494625.21 rows=1 width=24) (actual rows=1 loops=3) │ │ -> Parallel Seq Scan on large (cost=0.00..1417317.00 rows=10307760 width=16) (actual rows=33333333 loops=3) │ │ Filter: (tz > '2018-09-23 14:47:30.298302-07'::timestamp with time zone) │ │ Planning Time: 0.121 ms │ │ JIT: │ │ Functions: 8 │ │ Inlining: true │ │ Optimization: true │ │ Execution Time: 2440.328 ms │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (13 rows) Time: 2440.891 ms (00:02.441) postgres[24261][1]=# set jit = 0; postgres[24261][1]=# EXPLAIN (ANALYZE, TIMING OFF) SELECT min(tz), sum(data_double), max(data_double) FROM large WHERE tz > '2018-09-23 14:47:30.298302-07'; ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Finalize Aggregate (cost=1495625.42..1495625.43 rows=1 width=24) (actual rows=1 loops=1) │ │ -> Gather (cost=1495625.20..1495625.41 rows=2 width=24) (actual rows=3 loops=1) │ │ Workers Planned: 2 │ │ Workers Launched: 2 │ │ -> Partial Aggregate (cost=1494625.20..1494625.21 rows=1 width=24) (actual rows=1 loops=3) │ │ -> Parallel Seq Scan on large (cost=0.00..1417317.00 rows=10307760 width=16) (actual rows=33333333 loops=3) │ │ Filter: (tz > '2018-09-23 14:47:30.298302-07'::timestamp with time zone) │ │ Planning Time: 0.041 ms │ │ Execution Time: 4140.116 ms │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (9 rows) Time: 4140.499 ms (00:04.140)