Menu
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?
562
A concatenated index is one index across multiple columns. The ordering of a two-column index is therefore like the ordering of a telephone directory: it is first sorted by surname, then by first name.
Fortunately, PostgreSQL has the ability to combine multiple indexes (including multiple uses of the same index) to handle cases that cannot be implemented by single index scans. The system can form AND and OR conditions across several index scans.
A “composite index”, also known as “concatenated index”, is an index on multiple columns in a table.
A multicolumn GiST index can be used with query conditions that involve any subset of the index's columns. Conditions on additional columns restrict the entries returned by the index, but the condition on the first column is the most important one for determining how much of the index needs to be scanned.
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.
118
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
