SQLize Online / PHPize Online  /  SQLtest Online

set random_page_cost=1.1; CREATE TABLE mytable(id bigserial PRIMARY KEY, kind text, param1 text, created_at timestamptz); INSERT INTO mytable(kind, param1, created_at) SELECT q1.v, q1.p, q2.g + '12 hour'::interval * q1.p FROM (VALUES ('kind1', 0), ('kind1', 1), ('kind1', 2), ('kind1', 3), ('kind1', 4), ('kind2', 3), ('kind2', 5) ) AS q1(v,p), (SELECT generate_series('2020-01-01'::timestamptz, '2021-01-01', '1 hour')) AS q2(g); vacuum analyze mytable; VALUES ('A-la your original query, before index creation'); explain (analyze, verbose, buffers, timing) SELECT id FROM ( SELECT id, ROW_NUMBER() OVER( PARTITION BY kind, param1 ORDER BY created_at desc ) AS rn FROM mytable WHERE kind= 'kind1' AND param1 IN (1::text, 2::text, 3::text) ) t WHERE t.rn = 1; VALUES ('A kind of more intuitive query, before index creation'); explain (analyze, verbose, buffers, timing) SELECT DISTINCT ON (kind, param1) id FROM mytable WHERE kind='kind1' AND param1 IN (1::text, 2::text, 3::text) ORDER BY kind DESC, param1 DESC, created_at DESC; create index on mytable(kind, param1, created_at, id) ; VALUES ('A-la your original query, with a reasonable index'); explain (analyze, verbose, buffers, timing) SELECT id FROM ( SELECT id, ROW_NUMBER() OVER( PARTITION BY kind, param1 ORDER BY created_at desc ) AS rn FROM mytable WHERE kind= 'kind1' AND param1 IN (1::text, 2::text, 3::text) ) t WHERE t.rn = 1; VALUES ('The more intuitive query, with the index (all the next are with the index)'); explain (analyze, verbose, buffers, timing) SELECT DISTINCT ON (kind, param1) id FROM mytable WHERE kind='kind1' AND param1 IN (1::text, 2::text, 3::text) ORDER BY kind DESC, param1 DESC, created_at DESC; VALUES ('Fixed set of key parameters with ORDER BY/LIMIT created_at'); explain (analyze, verbose, buffers, timing) WITH v(k,p) AS (VALUES ('kind1', 1), ('kind1', 2), ('kind1', 3)) SELECT (SELECT id FROM mytable WHERE (kind, param1) = (vi.k, vi.p::text) ORDER BY created_at DESC LIMIT 1) FROM v AS vi; VALUES ('An intuitive approach to a range key parameters with ORDER BY/LIMIT created_at'); VALUES ('Note no index skip scans, get a lot of tuples from the index'); explain (analyze, verbose, buffers, timing) WITH v(k,p) AS (SELECT DISTINCT m.kind, m.param1 FROM mytable m WHERE m.kind='kind1' AND m.param1 BETWEEN 1::text AND 3::text) SELECT (SELECT id FROM mytable WHERE (kind, param1) = (vi.k, vi.p::text) ORDER BY created_at DESC LIMIT 1) FROM v AS vi; VALUES ('The RECURSIVE/direct key search algorithm for a range key parameters with ORDER BY/LIMIT created_at'); explain (analyze, verbose, buffers, timing) WITH RECURSIVE vs(k,p) AS ( SELECT DISTINCT m.kind, m.param1 FROM mytable m WHERE (m.kind, m.param1) BETWEEN ('kind1', 1::text) AND ('kind1', 3::text) ORDER BY m.kind, m.param1 LIMIT 1), v(k,p) AS NOT MATERIALIZED ( SELECT k, p FROM vs UNION ALL (SELECT m.kind, m.param1 FROM v JOIN LATERAL (SELECT kind, param1 FROM mytable WHERE (mytable.kind, mytable.param1) > (v.k, v.p) -- AND (mytable.kind, mytable.param1) <= ('kind1', 3::text) ORDER BY kind, param1 LIMIT 1) AS m(kind, param1) ON true WHERE (m.kind, m.param1) <= ('kind1', 3::text) ) ) SELECT (SELECT id FROM mytable WHERE (kind, param1) = (vi.k, vi.p::text) ORDER BY created_at DESC LIMIT 1) FROM v AS vi;