![]() ![]() ![]() As a result, parameterized query clauses do not work with a partial index. Matching takes place at query planning time, not at run time. (Not only is such a general theorem prover extremely difficult to create, it would probably be too slow to be of any real use.) The system can recognize simple inequality implications, for example “ x < 1” implies “ x < 2” otherwise the predicate condition must exactly match part of the query's WHERE condition or the index will not be recognized as usable. PostgreSQL does not have a sophisticated theorem prover that can recognize mathematically equivalent expressions that are written in different forms. To be precise, a partial index can be used in a query only if the system can recognize that the WHERE condition of the query mathematically implies the predicate of the index. ![]() However, keep in mind that the predicate must match the conditions used in the queries that are supposed to benefit from the index. PostgreSQL supports partial indexes with arbitrary predicates, so long as only columns of the table being indexed are involved. The order 3501 might be among the billed or unbilled orders.Įxample 11.2 also illustrates that the indexed column and the column used in the predicate do not need to match. SELECT * FROM orders WHERE order_nr = 3501 Note that this query cannot use this index: Yet, if there are relatively few unbilled orders, using this partial index just to find the unbilled orders could be a win. ![]() This is not as efficient as a partial index on the amount column would be, since the system has to scan the entire index. SELECT * FROM orders WHERE billed is not true AND order_nr 5000.00 The command to create the index would look like this:ĬREATE INDEX orders_unbilled_index ON orders (order_nr)Ī possible query to use this index would be: If you have a table that contains both billed and unbilled orders, where the unbilled orders take up a small fraction of the total table and yet those are the most-accessed rows, you can improve performance by creating an index on just the unbilled rows. Obviously, setting up partial indexes for this kind of scenario will require a lot of care and experimentation. This results in the same advantages as listed above, but it prevents the “ uninteresting” values from being accessed via that index, even if an index scan might be profitable in that case. Such indexes can be recreated occasionally to adjust for new data distributions, but this adds maintenance effort.Īnother possible use for a partial index is to exclude values from the index that the typical query workload is not interested in this is shown in Example 11.2. Observe that this kind of partial index requires that the common values be predetermined, so such partial indexes are best used for data distributions that do not change. The following query cannot use the partial index, as it uses an IP address that is excluded from the index: Here the query's IP address is covered by the partial index. WHERE NOT (client_ip > inet '192.168.100.0' ANDĪ typical query that can use this index would be: To create a partial index that suits our example, use a command such as this:ĬREATE INDEX access_log_client_ip_ix ON access_log (client_ip) If your searches by IP are primarily for outside accesses, you probably do not need to index the IP range that corresponds to your organization's subnet. Most accesses originate from the IP address range of your organization but some are from elsewhere (say, employees on dial-up connections). Suppose you are storing web server access logs in a database. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |