Обсуждение: Optimize query
Hi
How would you optimize a query with greater than in where clause eg
select * from table1 where id > 1000
and there is an index on id column
regards
Hi Yambu, the btree index type is optimized for this kind of operators (>, >=, <, <=, =).
For deep understanding on how to plan an query optimization I would recommend https://classroom.google.com/c/MTQ4MzczNDExMjM4 and https://use-the-index-luke.com.
Another tip is use in the select clause only the columns that are significant to answer the question you user is making executing this query.
Sorry for my foreign english.
Best regards.
Raúl.
El mar, 15 dic 2020 a las 11:18, Yambu (<hyambu@gmail.com>) escribió:
HiHow would you optimize a query with greater than in where clause egselect * from table1 where id > 1000and there is an index on id columnregards
On 12/15/20 8:17 AM, Yambu wrote:
The question as written is unanswerable.
- Why do you think it needs to be optimized?
- What is the table cardinality, and the query cardinality?
- What does EXPLAIN say?
- Have you run ANALYZE on table1?
Bottom line: Postgres might think that it is more efficient to scan the whole table.
HiHow would you optimize a query with greater than in where clause egselect * from table1 where id > 1000and there is an index on id column
The question as written is unanswerable.
- Why do you think it needs to be optimized?
- What is the table cardinality, and the query cardinality?
- What does EXPLAIN say?
- Have you run ANALYZE on table1?
Bottom line: Postgres might think that it is more efficient to scan the whole table.
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.