Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.
29

Install the additional module btree_gist as is mentioned in the manual at the location you linked to: You can use the btree_gist extension to define exclusion constraints on plain scalar data types, which can then be combined with range exclusions for maximum flexibility. For example, after btree_gist is installed, the following constraint will ...


12

Timestamps with B-tree index I suggest a third option: as long as your table holds two timestamp columns (which seem to be defined NOT NULL) I would use a single multicolumn index with opposed sort order (if no other considerations apply): CREATE INDEX reservations_range_idx ON reservations using gist(starts_at, ends_at DESC); More in these related answer:...


10

First, use ST_SetSRID(ST_MakePoint(c.longitude, c.latitude),4326)::geography instead of ST_GeographyFromText('SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')') Per documentation: ST_MakePoint while not being OGC compliant is generally faster and more precise than ST_GeomFromText and ST_PointFromText. It is also easier to use if you ...


9

You may consider using a GIST index based on using the function ll_to_earth. This index will allow for fast "nearby" searches. CREATE INDEX ON locs USING gist (ll_to_earth(lat, lng)); Once you have this index, your query should be done in a different way. Your (lat, lng) pairs need to be converted to the earth type, and compared with the indexed ...


6

The manual for the pg_trgm module has some advice for your question here: As a rule of thumb, a GIN index is faster to search than a GiST index, but slower to build or update; so GIN is better suited for static data and GiST for often-updated data. The FASTUPDATE feature of GIN indexes (introduced in Postgres 8.4, ON by default) should be interesting ...


6

Q: Does PostgreSQL uses a hash function for checking equality of integer arrays or does it perform a brute-force algorithm comparing one-by-one the elements of the array? Not according to Array Functions and Operators in the doc: Array comparisons compare the array contents element-by-element, using the default B-tree comparison function for the ...


5

Whenever you need to examine the structure of your database via code, always think "I should look at information_schema or pg_catalog". information_schema contains a standardized schema (66 views), whereas pg_catalog is PostgreSQL-specific, but contains more info (97 tables or views). select * from pg_catalog.pg_indexes where indexdef ~* '\ygist\y'...


4

I suggest a couple of important improvements for dealing with a million rows: SELECT br.duration FROM ( VALUES ('[2016-09-06 03:45:00+00, 2016-09-06 14:45:00+00)'::tstzrange) , ('[2016-09-06 14:45:00+00, 2016-09-06 15:45:00+00)') -- more items ) br(duration) WHERE EXISTS ( SELECT FROM booking WHERE tstzrange(ts_start, ts_end)...


4

In my opinion if you do not share how you did the test, it's very hard to give you an answer. Let see an example of what I mean. Sorry for I used a postgres 11 but the conclusions are the same: This is a new db, there is nothing running against the instance: test=# CREATE EXTENSION pg_trgm; CREATE EXTENSION test=# create table test_trgmidx (col1 ...


3

I used this simpler test setup instead: CREATE TABLE sampledata AS SELECT row_number() OVER ()::int , extract(hour FROM ts)::int AS hour , ts FROM generate_series (timestamptz '2004-03-07' , timestamptz '2004-03-11' , interval '1 second') ts; -- instead of millisecond CREATE INDEX idx_hourts_btree ON ...


3

The point of reference comes from the cafe in the center, so you can use a subquery to retrieve it from the addresses table instead of the manual input: SELECT c.*, a.*, ST_Distance(t.lonlat, a.lonlat) AS distance -- pick columns you need FROM addresses a JOIN cafes c ON c.id = a.cafe_id , (SELECT lonlat FROM addresses WHERE ...


3

GIN indexes do not work with any jsonb operators EXCEPT ? ?& ?| @>. Clearly, your use of comparison operators >= and <= are not on that list. And all of those operators that it could help are also not on the list (meaning the index won't do anything) afaik. That means needing indexed comparison operators, you'll need a btree. CREATE INDEX ON ...


3

Alternative answer with PostGIS If you're using 10 million rows. You probably need to step up and upgrade to PostGIS. Convert your points to geography types. I assume they're in SRID 4326 anyway if they come from GPS. For this you can use geometery(point)::geography, or if you store in lat/long you can use ST_MakePoint Create an index on the new geom ...


3

Off the top of my head, here are a few things to do, in no particular order. VACUUM FREEZE ANALYZE Add the indexes that you think are going to help. Execute as many queries as you can. Look at pg_stat_user_indexes to see which queries are being used. Look at the EXPLAIN ANALYZE plan for each query. Review https://wiki.postgresql.org/wiki/...


2

1) as you already have discovered, you can't use b-tree as the index size is bigger than the page size 2) given: As a rule of thumb, a GIN index is faster to search than a GiST index, but slower to build or update; so GIN is better suited for static data and GiST for often-updated data. You would have to use GIN. And no, GIN doesn't use hash functions ...


2

if someone can't or doesn't want to use this: CREATE EXTENSION btree_gist; As it was in my case, because Django 1.11 ORM does not support this index and I didn't want to write SQL outside Django. I used something similar to: EXCLUDE USING gist ( int4range(userid, userid, '[]') WITH =, startend WITH && ) '[]' is used to make sure both ...


2

The question is what is the reason the index is not used with the tstzrange containment operator and if there is a way to make it work. The reason is quite trivial. B-tree indexes do no support the containment operator @>. Neither for range types like tstzrange nor for any other type (including array types). The manual: ... a btree operator class ...


2

As far as my research goes, postgresql is not able to rewrite containment check into an expression that could be matched using btree index, i.e. esl1.created_at >= now() - interval '1 hour' AND esl1.created_at < now() + interval '1 hour' when written such way, the query is executed using indexes: Index Scan using event_seating_lookup_created_at_idx ...


2

Looks like a bug in either the GiST index implementation, the ll_to_earth() function (or maybe the specific operator / operator class). You have pretty much ruled out plain index corruption by trying REINDEX. First try to upgrade to a current version of Postgres and see if this fixes your problem. 9.5 is getting old (and the current point release is 9.5.15)....


2

I have seen GIN indexes to generally perform much faster than GiST for these queries. Try this index instead: CREATE INDEX price_item_occurrence_name_trgm_gin idx ON price_item_occurrence USING GIN (f_unaccent(name) gin_trgm_ops); Related: Does PostgreSQL support “accent insensitive” collations? Using ILIKE with unaccent and with only right end wildcard ...


2

Since you already store the values (rmin, rmax, gmin, gmax, bmin, bmax) for the image column, a btree index on those covers equality checks just fine: CREATE INDEX foo1 ON icons (rmin, rmax, gmin, gmax, bmin, bmax); This query will use the index: SELECT COUNT(*) FROM avatars a JOIN icons i WHERE a.id = 123 AND (a.rmin, a.rmax, a.gmin, a.gmax, a....


2

It all depends ... Well, a couple of things are certain: For the presented use case, a trigram GIN index should deliver best read performance. Not GiST (slower) and not text_pattern_ops (only applicable for left-anchored / leading patterns). So focus your efforts around this Index: CREATE INDEX name_gin_trgm_idx ON residentfiles USING gin (name ...


1

Thanks for the question. In this case I would use pg_trgm. Here is blog post with samples, and explanation: https://niallburkley.com/blog/index-columns-for-like-in-postgres/ In short: CREATE EXTENSION pg_trgm; CREATE INDEX residentfiles_parentpath_trgm_idx ON residentfiles USING gin (parentpath gin_trgm_ops); EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM ...


1

An EXCLUSION constraint with && (Overlaps) needs to be GIST or SP-GIST because the default index is btree and its operator class does not support && (Overlaps). From the docs on Interfacing Extensions To Indexes GiST indexes are more flexible: they do not have a fixed set of strategies at all. Instead, the “consistency” support routine of ...


1

I think this would actually work, but I would follow the same format used in the docs for consistency on the decompress version that doesn't require compression, PG_FUNCTION_INFO_V1(my_decompress); Datum my_decompress(PG_FUNCTION_ARGS) { PG_RETURN_POINTER(PG_GETARG_POINTER(0)); } My assumption here is that because you don't actually need to initialize ...


1

Sorry for the delay on this one. I'm not sure if protocol dictates that I answer my own question to post these details, but comments (to Erwin's answer) don't provide enough space. So I was noticing pretty poor performance using the above individual indexes when I'd run queries. I have 2 main uses cases: Query for all things in the 'public sphere' or my ...


1

Is it possible to use a single index that does trigram searches, but also exact match on user_id and type where they can optionally be NULL. Yes, NULL is included in indexes. And you can search for it like for any other value. Yes, you can have a multicolumn trigram GiST index. But GiST indexes typically don't make sense for the data type integer. Btree ...


1

The minute (no pun intended) there is a difference of 2 milliseconds between, PostgreSQL abandons the gist index, keeping performance at an optimum. QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using idx_hourts_btree ...


1

Thanks to Evan Carrol I figured I simply needed to run # VACUUM FULL ANALYZE store; on my table (which was named store) and then database started using the index on queries.


1

smlar Extension I was able to create a gist index on a text[] column using the smlar extension (written by Teodor Sigaev of text search fame) # git clone git://sigaev.ru/smlar.git # cd smlar/ # PATH=/usr/pgsql-9.6/bin:$PATH make USE_PGXS=1 # PATH=/usr/pgsql-9.6/bin:$PATH make USE_PGXS=1 install # psql postgres=# create extension smlar; postgres=# create ...


Only top voted, non community-wiki answers of a minimum length are eligible