2
Index Cond: ((service_costs_import_id = ANY ('{2066,2067,1267,1269,1268,1270,2068,1273,4996,5047}'::bigint[])) AND ((cost_type)::text = ANY ('{...}'::text[])) AND (date >= '2021-10-01 00:00:00'::timestamp without time zone) AND (date <= '2021-10-31 23:59:59.999999'::timestamp without time zone) AND ((service)::text = '...'::text))
It looks like your ...
2
The more pressing issue is the bad query. Shouldn't take > 1 sec in either instance. Your filter on a1."geom"::geography. This cast disables any plain index on addresses.geom - including the spatial index you have:
"idx_addresses_geom" gist (geom)
So we see ST_DWithin() only as FILTER while it should be the index condition to identify ...
2
One way: generate all sub-domains, top-level down, and take the first match:
WITH cte AS (
SELECT ord, string_agg(label, '.') OVER (ORDER BY ord ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS subdomain
FROM unnest(string_to_array('bla.bla.dd.cc.360.cn', '.')) WITH ORDINALITY AS x(label, ord)
ORDER BY ord DESC
OFFSET 1 -- skip tld
)
...
1
You will probably get errors about too many open files long before you reach 5000 queued connections, and so need to change the limit using ulimit -n or by otherwise altering the kernel settings. (Note that if your application needs max_client_conn to be that high, your application is almost certainly profoundly broken. But that wasn't your question)
You ...
1
As documented in the manual the complete formula for the cost value also includes a CPU part that is calculated per row
The estimated cost is computed as (disk pages read * seq_page_cost) + (rows scanned * cpu_tuple_cost). By default, seq_page_cost is 1.0 and cpu_tuple_cost is 0.01
The above is the formula for a Seq Scan thus the cost value for sequential ...
1
This would go twice as fast, or half as slow:
select
sum(
case when "timestamp" >= date_trunc('hour', now()) then 1 else 0 end
) txct_lasthour,
sum(
case when "timestamp" >= date_trunc('year', now()) then 1 else 0 end
) txct_lastyear
from transactions where account = 'abc';
1
Yes, you will be saving a byte per row, and possibly even more, if the next column is of a type with a type alignment greater than 1.
However, the NULL might make your queries more complicated or harder to read, which might be worse than the benefit of saving a little disk space.
I'd decide based on the latter: if your queries are still efficient and ...
Only top voted, non community-wiki answers of a minimum length are eligible