Skip to content

Instantly share code, notes, and snippets.

View eliank's full-sized avatar

Elian Krosse eliank

4 followers · 0 following
View GitHub Profile
@eliank
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
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
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
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
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
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
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
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
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