I have a query like this:
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;
this query takes around one two seconds to run, and we have at least 10 to 20 clients running this query over and over
the output of the EXPLAIN ANALYZE
is this:
Limit (cost=6192.97..6192.98 rows=1 width=244) (actual time=1086.744..1086.744 rows=0 loops=1)
-> Sort (cost=6192.97..6192.98 rows=1 width=244) (actual time=1086.743..1086.743 rows=0 loops=1)
Sort Key: id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on btr_orders (cost=0.00..6192.96 rows=1 width=244) (actual time=1086.736..1086.736 rows=0 loops=1)
Filter: ((NOT temporary) AND ((active_since IS NULL) OR (active_since <= '2017-06-13 14:59:29.528395'::timestamp without time zone)) AND (user_id = 40000000) AND ((premium_until > '2017-06-13 14:59:29.527964'::timestamp without time zone) OR (intermediate_premium_until > '2017-06-13 14:59:29.527964'::timestamp without time zone)))
Rows Removed by Filter: 154734
Total runtime: 1086.789 ms
although the rows filtered is actually 1, this query is really slow, probably because we are doing seq. scan. I tried to create an index for this query:
create index CONCURRENTLY btr_orders_search_index
on btr_orders (temporary, user_id, active_since, premium_until, intermediate_premium_until)
where temporary = false;
but of course didn't worked. I have a very limited knowledge on postgres, actually in database administration (I'm a backend developer) and we do not have a DBA.
My question is: how can I create an index that fits this query?
Edit
\d btr_orders
zenguard_api=# \d btr_orders
Table "public.btr_orders"
Column | Type | Modifiers
----------------------------+-----------------------------+---------------------------------------------------------
id | integer | not null default nextval('btr_orders_id_seq'::regclass)
active_since | timestamp without time zone |
affiliate_cookies | hstore |
affiliate_net_amount | integer |
affiliate_partner_id | integer |
closed_since | timestamp without time zone |
country_code | character varying(255) |
created_at | timestamp without time zone |
discount_id | integer |
first_charge_at_activation | boolean | default false
intermediate_premium_until | timestamp without time zone |
ip_country_code | character varying(255) |
merchant_account | character varying(255) | default 'ZenGuard'::character varying
order_reference | character varying(255) |
paid_premium_until | timestamp without time zone |
premium_since | timestamp without time zone |
premium_until | timestamp without time zone |
price_id | integer |
product_id | integer |
purchase_platform | character varying(255) |
temporary | boolean | default false
updated_at | timestamp without time zone |
user_id | integer |
migrated_from_old | boolean | default false
Indexes:
"btr_orders_pkey" PRIMARY KEY, btree (id)
\d btr_orders
. Also don't double quote identifiers, ever. It's bad practice.