Made by Developers and Non-Developers
The query fetches sales that were modified before 2019. There is no index on this field, so the optimizer generates an execution plan to scan the entire table.
Let’s say you have another field in this table with the time the sale was created. Since it’s not possible for a sale to be modified before it was created, adding a similar condition on the
created
field won’t change the result of the query. However, the optimizer might use this information to generate a better execution plan:db=# ( SELECT * FROM sale WHERE modified < '2019-01-01 asia/tel_aviv' AND created < '2019-01-01 asia/tel_aviv'; ); QUERY PLAN -------------------------------------------------------------------------------------------------- Index Scan using sale_created_ix on sale (cost=0.44..4.52 rows=1 width=276) Index Cond: (created < '2019-01-01 00:00:00+02'::timestamp with time zone) Filter: (modified < '2019-01-01 00:00:00+02'::timestamp with time zone)After we added the “Faux Predicate” the optimizer decided to use the index on the
created
field, and the query got much faster! Note that the previous predicate on themodified
field is still being evaluated, but it’s now being applied on much fewer rows.A “Faux Predicate” should not change the result of the query. It should only be used to provide more information to the optimizer that can improve the query performance. Keep in mind that the database has to evaluate all the predicates, so adding too many might make a query slower.
Command line tool for anonymizing database records
The command line tool for anonymizing database records by parsing a SQL dump file and build a new SQL dump file with masking sensitive/credential data.
Announcing Yugabyte DB 2.0 GA: Jepsen Tested, High-Performance Distributed SQL
We are excited to announce the general availability of Yugabyte DB 2.0! The highlight of this release is that it delivers production readiness for Yugabyte SQL (YSQL), our high-performance, fully-relational distributed SQL API. For those of you new to distributed SQL, Yugabyte DB is a Google Spanner-inspired, cloud-native distributed SQL database that is 100% open source. It puts a premium on high performance, data resilience, geographic distribution while ensuring PostgreSQL compatibility.
Schema Spy
Document your database simply and easily
Alternatives: