Add “Faux” Predicates: Postgres Optimization

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 the modified 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.

pgsql-http (Github)

HTTP client for PostgreSQL, retrieve a web page from inside the database.

SELECT content FROM http_get(‘http://httpbin.org/ip’);
content
—————————–
{“origin”:”24.69.186.43″} +
(1 row)

Functions
http_header(field VARCHAR, value VARCHAR) returns http_header
http(request http_request) returns http_response
http_get(uri VARCHAR) returns http_response
http_post(uri VARCHAR, content VARCHAR, content_type VARCHAR) returns http_response
http_put(uri VARCHAR, content VARCHAR, content_type VARCHAR) returns http_response
http_patch(uri VARCHAR, content VARCHAR, content_type VARCHAR) returns http_response
http_delete(uri VARCHAR) returns http_response
http_head(uri VARCHAR) returns http_response
http_set_curlopt(curlopt VARCHAR, value varchar) returns boolean
http_reset_curlopt() returns boolean
urlencode(string VARCHAR) returns text

FINE TUNING FULL TEXT SEARCH WITH POSTGRESQL 12

FLEXING POSTGRES 12

Trying to decide between to_tsqueryplainto_tsquery and phraseto_tsquery can be difficult. It was kind of straightforward in our case – we’re not searching on any phrases really.

The Postgres team decided to be helpful in this regard, especially when it comes to web applications, so they created websearch_to_tsquery. It basically treats the input as if it were entered into a Google search. To be dead honest I have no idea what’s happening under the covers here, but it’s supposed to be a bit more intelligent than plainto_tsquery and a little less strict than phraseto_tsquery.

Datatype for a URL in PostgreSQL

This is an extension for PostgreSQL that provides a uri data type. Advantages over using plain text for storing URIs include:

  • URI syntax checking
  • functions for extracting URI components
  • human-friendly sorting

The actual URI parsing is provided by the uriparser library, which supports URI syntax as per RFC 3986.

Note that this might not be the right data type to use if you want to store user-provided URI data, such as HTTP referrers, since they might contain arbitrary junk.