Concatenated index in postgresql

Question

So basically I'm matching addresses by matching strings within 2 tables

Table B has 5m rows so I really don't want to create new columns for it every time I want to match the addresses

So I thought about creating indexes instead, my current index to match addresses would look like:

CREATE INDEX matchingcol_idx  ON tableB USING btree (sub_building_name || ', ' || building_name )

However this does not work, it doesn't accept the concatenation bar

My update query would then equal = b.sub_building_name || ', ' || b.building_name

Without a new column and an index this would take multiple hours

Is there a way to achieve this without creating new concatenation columns?

What actually are you trying to do ? Do you want to speed up an update query using this index, or ordinary SELECT query ? Please show the SQL query to be optimized using this index. — krokodilko
– krokodilko, Commented Jul 10, 2018 at 16:42

user330315 · Accepted Answer · 2018-07-10 16:46:39Z

15

For an expression based index, you need to put the expression between parentheses:

CREATE INDEX matchingcol_idx  
   ON tableB USING btree ( (sub_building_name || ', ' || building_name) );

But that index will only be used if you use exactly the same condition in your where clause. Any condition only referencing one of the columns will not use that index.

answered Jul 10, 2018 at 16:46

user330315

Sign up to request clarification or add additional context in comments.

4 Comments

Luffydude Over a year ago

So any other matching I do will need me to create a new index. It definitely worked in a few minutes rather than a few hours, thanks!

user330315 Over a year ago

@Luffydude: maybe a multi-column index on (sub_building_name, building_name) is the better choice then. But without your query and the complete table definition this is impossible to answer

Luffydude Over a year ago

thing is, in order to match strings I will need to do some monkey business with substrings and lefts and uppers and commas and no commas in between so I'm not sure. I had to write at least 18 different update statements and it only got me 95% of strings lol

user330315 Over a year ago

@Luffydude: again: without the query this is impossible to answer. You should ask a new question that shows some sample data and what you want to do with that data.

Andrei Sura · Accepted Answer · 2023-03-01 15:23:46Z

For matching via LIKE or ILIKE you can create a gin index as described here https://niallburkley.com/blog/index-columns-for-like-in-postgres/

CREATE INDEX CONCURRENTLY idx_match_building 
   ON tableB USING gin ( (sub_building_name || ', ' || building_name) gin_trgm_ops);

Verify that the extension is enabled via this query

SELECT * FROM pg_extension where extname = 'pg_trgm';
+-------+---------+----------+--------------+----------------+------------+-----------+--------------+
| oid   | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition |
|-------+---------+----------+--------------+----------------+------------+-----------+--------------|
| 17458 | pg_trgm | 10       | 17455        | True           | 1.3        | <null>    | <null>       |
+-------+---------+----------+--------------+----------------+------------+-----------+--------------+

Relevant info about why concat will not cut it :) https://www.postgresql.org/message-id/1409893231867-5817884.post%40n5.nabble.com

Collectives™ on Stack Overflow

Concatenated index in postgresql

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related