eliank’s gists

eliank / postgres-the-unwanted-side-effects-of-limit-on-query-planning-8.rb

Last active May 23, 2016 13:03

	SomeModel.where(some_variable: %i(some_value another_value yet_another_value)).where(“some_date <= ?”, Time.zone.today).select(:id)
	.find_each_with_order(additional_order_column: :some_variable) do \|some_model_id\|
	# do something
	end

eliank / postgres-the-unwanted-side-effects-of-limit-on-query-planning-7.sql

Created May 23, 2016 11:58

SELECT “some_table”.”id” FROM “some_table” WHERE (some_variable IN (‘some_value’, ‘another_value’, ‘yet_another_value’) AND ((some_date <= ‘2016–05–01’ AND DATE_TRUNC(‘day’, updated_at) <= some_date) AND (“some_table”.”id” > 133891263636178370) ORDER BY “some_table”.”id” ASC, “some_table”.”some_variable” ASC LIMIT 1000;

eliank / postgres-the-unwanted-side-effects-of-limit-on-query-planning-6.sql

Created May 23, 2016 11:57

	some_table_some_index_query AS (
	SELECT “some_table”.”id” FROM “some_table” WHERE (some_variable IN (‘some_value’, ‘another_value’, ‘yet_another_value’) AND ((some_date <= ‘2016–05–01’ AND DATE_TRUNC(‘day’, updated_at) <= some_date)
	)
	SELECT * FROM “some_table_some_index_query” WHERE (“some_table”.”id” > 123456789) ORDER BY “id” ASC LIMIT 1000;

eliank / postgres-the-unwanted-side-effects-of-limit-on-query-planning-5.sql

Created May 23, 2016 11:56

	moneybird_app=> EXPLAIN ANALYZE SELECT “some_table”.”id” FROM “some_table” WHERE (some_variable IN (‘some_value’, ‘another_value’, ‘yet_another_value’) AND ((some_date <= ‘2016–05–01’ AND DATE_TRUNC(‘day’, updated_at) <= some_date) AND (“some_table”.”id” > 123456789) ORDER BY “some_table”.”id” ASC;
	— REMOVED DETAILS
	Bitmap Index Scan on some_table_index (cost=0.00..1362.84 rows=53272 width=0) (actual time=30.145..30.145 rows=19102 loops=1)
	— REMOVED DETAILS
	Planning time: 0.527 ms
	Execution time: 4533.796 ms

eliank / postgres-the-unwanted-side-effects-of-limit-on-query-planning-4.sql

Created May 23, 2016 11:55

moneybird_app=> ANALYZE some_table;

eliank / postgres-the-unwanted-side-effects-of-limit-on-query-planning-3.sql

Created May 23, 2016 11:53

	moneybird_app=> EXPLAIN ANALYZE SELECT “some_table”.”id” FROM “some_table” WHERE (some_variable IN (‘some_value’, ‘another_value’, ‘yet_another_value’) AND ((some_date <= ‘2016–05–01’ AND DATE_TRUNC(‘day’, updated_at) <= some_date) AND (“some_table”.”id” > 133891263636178370) ORDER BY “some_table”.”id” ASC LIMIT 1000;
	— REMOVED DETAILS
	Index Scan using some_table_pkey on some_table (cost=0.43..1295055.42 rows=75277 width=8) (actual time=0.771..35490.356 rows=1000 loops=1)
	— REMOVED DETAILS
	Planning time: 2.049 ms
	Execution time: 35521.964 ms

eliank / postgres-the-unwanted-side-effects-of-limit-on-query-planning-2.sql

Created May 23, 2016 11:50

CREATE INDEX

CREATE INDEX some_table_some_index ON some_table USING btree (some_variable, some_date) WHERE some_variable IN ('some_value', 'another_value', 'yet_another_value') AND DATE_TRUNC('day', updated_at) <= some_date;

eliank / postgres-the-unwanted-side-effects-of-limit-on-query-planning-1.sql

Last active May 23, 2016 11:48

	moneybird_app=> EXPLAIN ANALYZE SELECT "some_table"."id" FROM "some_table" WHERE (some_variable IN ('some_value', 'another_value', 'yet_another_value') AND ((some_date <= '2016-05-01' AND DATE_TRUNC('day', updated_at) <= some_date) AND ("some_table"."id" > 123456789) ORDER BY "some_table"."id" ASC LIMIT 1000;
	-- REMOVED DETAILS
	Index Scan using some_table_pkey on some_table (cost=0.43..1295055.42 rows=75277 width=8) (actual time=0.771..36133.574 rows=1000 loops=1)
	-- REMOVED DETAILS
	Planning time: 2.200 ms
	Execution time: 36134.375 ms

eliank / active_record_batches.rb

Created May 23, 2016 07:53

	module ActiveRecord
	module Batches
	def find_each_with_order(options = {})
	if block_given?
	find_in_batches_with_order(options) do \|records\|
	records.each { \|record\| yield record }
	end
	else
	enum_for :find_each, options do
	options[:start] ? where(table[primary_key].gteq(options[:start])).size : size

Elian Krosse eliank