To support that SELECT statement, you first need to figure out which of the three conditions are selective, that is, filter out a substantial number of rows. For each selective condition, create a B-tree index on only that column, so that you end up with up to three single-column indexes.
PostgreSQL can combine these indexes to speed up the query. A multi-...
Create Table Index in where clause sequence and try it.
where resource_id In (:resourceidlist) and date in (:dates) and
node_id in (:nodeIds)
Table index like
Table Index(resource_id, date , node_id )
A basic, 100 % equivalent rewrite of your "ugly-OR" query with UNION:
FROM table1 c
JOIN table2 l USING (customer_id)
JOIN table3 cal USING (customer_id)
WHERE l.customer_group_id = 'loyalty'
AND c.loyalty_number = '123456789'
FROM table1 c
JOIN table2 l USING (customer_id)
JOIN table3 ...
INSERT ... ON CONFLICT is as efficient as it can be, but the question is beside the point.
It may well be that an UPDATE is faster, but that UPDATE wouldn't do the right thing for 0.1% of the rows, so it is not a solution.
Note: it is always possible to be faster if you don't have to be correct.
The tolerable one with the OR got lucky, because it found 100 matching rows with types of 1 or 3, before it found any of type 2 which had to be checked against the other table. The intolerable one apparently did have to do the check against the other table, and it does it in a very slow way, by looping over all the rows in it. Now it should use a hashed ...
Slowness other issue but I can tell you about the query time out due to recovery error.
canceling statement due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed.
Its happening because wal files are getting applied on read replica at time when select queries are running. This will terminate the queries.
It's typically a good idea to split up that ugly OR in to a UNION query. See:
Why is an OR statement slower than UNION?
The first SELECT of the UNION query should melt down to milliseconds with this partial multicolumn index:
CREATE INDEX ON changes (user_id, counter)
WHERE type IN (1, 3);
And after adding ORDER BY counter LIMIT 100. Since the outer ...
My theory on what is happening here is that for an INSERT only table, vacuum only needs to visit the parts of the table dirtied by the inserts, and can skip visiting the indexes at all.
But if it finds even one (in v11) dead tuple, then it needs to scan the entirety of all the indexes, and that can take a long time. I'm surprised it takes over 2 hours, but ...
This may well be transaction ID wraparound, which must scan and freeze the rows inserted since the last wraparound.
You should change your nightly run to VACUUM (FREEZE) to distribute that load, particularly if it is an insert-only table, where proactive freezing doesn't expend unnecessary resources. See the documentation for a detailed discussion of the ...
It thinks the generic plan you show (with the $1 in it) will be slightly faster than the custom plan (with the actual value in it), 104.57 vs 105.26. So after running a number of custom plans and thinking there is no benefit, it doesn't think it is worthwhile making a new custom plan each time. Of course the estimate is way off, but the estimate is what it ...
So, warning up-front: I don't have a fix for your problem, this is just explaining what's going on.
It comes down to the selectivity of your WHERE. If the planner thinks very few rows will satisfy the WHERE, it makes sense to use the GIN indexes, get all rows that match, and then perform a sort. But if the planner thinks lots of rows will pass the filter, it ...
You need index support to be fast. Tricky without re-designing your table. The following solution should perform excellently (microseconds instead of seconds), but requires some skill. Buckle up.
Expression index on IMMUTABLE function
Just take a few leading array elements, say 8. That should be very selective already. More would just make the index bigger, ...
A GiST or (even better) SP-GiST expression index on an inclusive timestamp range should work wonders.
CREATE INDEX events_right_idx ON events USING spgist (tsrange(time_start, time_end, ''));
Rewrite your query with the "range contains" operator @> and match the indexed expression (exactly equivalent to your original):
The cost estimates of the fast and slow plans are pretty close together, so just random variation in the sampling method from ANALYZE to ANALYZE could push one over the other in an unpredictable fashion. But that also means you don't need to get much of a change to push it back over to the actually fast one.
The misestimation is a compounding of several ...
Please update your question with the version of PostgreSQL running on both servers, and the DDL for the item, item_part, and price tables. You can use a fiddle if the definitions are large. Also, please explain the relationship between the servers -- physical replicas, logical replicas, copies made by backup & restore, etc.
Comparing your explain plans, ...