clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 648391 fiddles created (15115 in the last week).

CREATE TABLE users ( user_id integer PRIMARY KEY ) ; CREATE TABLE btr_orders ( id SERIAL PRIMARY KEY, active_since timestamp without time zone, premium_until timestamp without time zone, intermediate_premium_until timestamp without time zone, user_id integer NOT NULL REFERENCES users(user_id), temporary boolean NOT NULL DEFAULT true, some_cargo text ) ;
 hidden batch(es)


INSERT INTO users (user_id) SELECT generate_series(1, 5000) AS user_id ; INSERT INTO users (user_id) VALUES (40000000) ;
5000 rows affected
1 rows affected
 hidden batch(es)


INSERT INTO btr_orders (active_since, premium_until, intermediate_premium_until, user_id, temporary) SELECT active_since, active_since + interval '1 day' AS premium_until, active_since + interval '12 hours' AS intermediate_premium_until, case when random() < 0.5 then 40000000 else random()*4000+1 end AS user_id, random() < 0.5 AS temporary FROM generate_series(date '2017-01-01', date '2017-12-31', interval '3 minutes') AS x(active_since);
174721 rows affected
 hidden batch(es)


EXPLAIN ANALYZE SELECT "btr_orders".* FROM "btr_orders" WHERE "btr_orders"."user_id" = 40000000 AND ( premium_until > now() OR intermediate_premium_until > now() ) AND ( active_since IS NULL OR active_since <= now() ) AND "btr_orders"."temporary" = false ORDER BY "btr_orders"."id" DESC LIMIT 1;
QUERY PLAN
Limit (cost=0.42..201.70 rows=1 width=65) (actual time=26.414..26.414 rows=1 loops=1)
-> Index Scan Backward using btr_orders_pkey on btr_orders (cost=0.42..11876.08 rows=59 width=65) (actual time=26.412..26.412 rows=1 loops=1)
Filter: ((NOT temporary) AND (user_id = 40000000) AND ((active_since IS NULL) OR (active_since <= now())) AND ((premium_until > now()) OR (intermediate_premium_until > now())))
Rows Removed by Filter: 96062
Planning time: 0.150 ms
Execution time: 26.433 ms
 hidden batch(es)


CREATE INDEX quite_simple_idx ON btr_orders (user_id, id) WHERE NOT temporary ;
 hidden batch(es)


EXPLAIN ANALYZE SELECT "btr_orders".* FROM "btr_orders" WHERE "btr_orders"."user_id" = 40000000 AND ( premium_until > now() OR intermediate_premium_until > now() ) AND ( active_since IS NULL OR active_since <= now() ) AND "btr_orders"."temporary" = false ORDER BY "btr_orders"."id" DESC LIMIT 1;
QUERY PLAN
Limit (cost=0.29..19.15 rows=1 width=65) (actual time=9.681..9.681 rows=1 loops=1)
-> Index Scan Backward using quite_simple_idx on btr_orders (cost=0.29..1546.49 rows=82 width=65) (actual time=9.679..9.679 rows=1 loops=1)
Index Cond: (user_id = 40000000)
Filter: (((active_since IS NULL) OR (active_since <= now())) AND ((premium_until > now()) OR (intermediate_premium_until > now())))
Rows Removed by Filter: 23963
Planning time: 0.179 ms
Execution time: 9.700 ms
 hidden batch(es)