![]() Note 1: This index can also be used if the search pattern starts with % But there is a simple method to rewrite that query without the pesky OR: Now for a more stupid variant of the above query:Īgain, a bitmap index scan is used. Moreover, it uses much more RAM each of these bitmaps can use up to work_mem memory.Ī multi-column index on (id, a_val) won’t help at all with this query, so there is no cheaper way to execute it. Note, however, that a bitmap index scan is more expensive than a normal index scan, since it has to build the bitmap. PostgreSQL can actually use an index scan for the query, because it can combine the bitmaps for both indexes with a “bitmap OR”. Now for an example of an OR in a WHERE clause that is still pretty nice: Unfortunately you usually find the OR where it hurts: in the WHERE clause. So if your OR appears in a CASE expression in the SELECT list, don’t worry. The “good” ORĪn OR is fine in most parts of an SQL query: if it is not used to filter out rows from your query result, it will have no negative effect on query performance. Have a look at the documentation if you don’t understand text_pattern_ops. Suppose that we want to run queries with equality and LIKE conditions on the text columns, so we need some indexes:ĬREATE INDEX a_val_idx ON a(a_val text_pattern_ops) ĬREATE INDEX b_val_idx ON b(b_val text_pattern_ops) We’ll use this simple setup for demonstration:ĬREATE TABLE a(id integer NOT NULL, a_val text NOT NULL) ĬREATE TABLE b(id integer NOT NULL, b_val text NOT NULL) ĪLTER TABLE b ADD FOREIGN KEY (id) REFERENCES a ![]() In this article I’ll explore “good” and “bad” ORs and what you can do to avoid the latter. But you should be aware of the performance implications. Of course there is a reason why there is an OR in SQL, and if you cannot avoid it, you have to use it. PostgreSQL query tuning is our daily bread at CYBERTEC, and once you have done some of that, you’ll start bristling whenever you see an OR in a query, because they are usually the cause for bad query performance. Performance query sql help © Laurenz Albe 2018
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |