|
From: gzh <gzhcoder@xxxxxxx> Sent: Wednesday,
12 October 2022 9:30 PM
> Who can tell me which solution is better below：
> Solution 1: Change the configuration parameters > set enable_seqscan = off > Solution 2: Add DISTINCT clause to SQL > explain analyze select DISTINCT 2 from analyze_word_reports where (cseid = 94) limit 1;
> If I don't want to change SQL, is Solution 1 OK?
Both solutions are ugly
enable_seqscan is a really blunt instrument and may affect the rest of your system as well as just this query. Queries that boil down to "select * from partition" are now encouraged to use the index in a useless manor. A small table (e.g. to hold application
settings) now has to do a primary key lookup when all rows fit on the first page anyway.
distinct+limit is really just trying to convince the v12 planner that it can bail out after the first row found at all levels. Having both is superfluous as they individually
end up at the same result. it may not work in v13/14/15/... or even be needed. Have you tried it on your v12? My data may be different enough to your data that it doesn't work anyway. What it does in the old postgres version is anyone's guess.
Solution 1 I'd treat as an emergency stop gap to buy time to find a better solution. The patient is no longer bleeding out and the path forward can be considered. If you're not going to change the app, then the only other choice is play with other system
wide settings (like random_page_cost). Not as blunt as enable_seqscan but still affects all queries, not just this one.
|
