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