0

Suppose I have a table with column_a and column_b, containing 100k records. I need to match either column_a OR column_b.

Will it be faster to create a new table containing only one column to match but twice the records?
Instead of this:

table_a

id  column_a  column_b
--  --------  --------
1   foo       bar

SELECT ... WHERE "column_a" LIKE '%blah%' OR "column_b" LIKE '%blah%'
# will iterate through 100k records

I'll have this:

table_b

id  column
--  ------
1   foo
2   bar

SELECT ... WHERE "column" LIKE '%blah%'
# will iterate through 200k records

What is cheaper, text matching or iteration?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    dear downvoters, can you please comment your hard work? –  Apr 18 '13 at 21:02
  • 1
    Not a downvoter, but "Just too lazy to write a test, so will ask here" isn't a good way to start a question. – Paul Bellora Apr 18 '13 at 21:03
  • 1
    Not only is "Just too lazy to write a test, so will ask here..." a terrible way to start a question, but a performance question like this is something you **have to** just try on your own, because there are so many variables that can affect performance. – Adam Maras Apr 18 '13 at 21:04
  • I should have phrased that better - I'm guessing the downvotes are for lack of research. – Paul Bellora Apr 18 '13 at 21:05
  • 1
    I didn't down-vote, but I would guess it's because of your first line `Just too lazy to write a test, so will ask here... :)` If you are too lazy, why should they do the work for you? Sounds like you are perfectly capable to perform the test... – digitaljoel Apr 18 '13 at 21:05
  • well, mostly it wanted to mean that somebody already did such a decision after doing same test... :) –  Apr 18 '13 at 21:07
  • My guess: about the same speed – Bohemian Apr 18 '13 at 21:08

1 Answers1

3

It also depends on the column types, size of columns, data distribution, other columns in the table and more. I guess you were just too lazy to tell us all those tedious details.

Generally, the fastest way is table_b with a trigram GIN index to support the non-anchored LIKE expression. The index makes the difference. And the query style will be another important factor. Utilize LIMIT 1 or EXISTS for table_b.

More details in this closely related answer:
PostgreSQL LIKE query performance variations

I am just too lazy to write a more detailed answer. Run a test. :)

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • @slivu Note that `GIN` trigram indexes are only supported on very recent PostgreSQL versions, you may need to upgrade. You didn't include your PostgreSQL version so I can't tell you for sure; look at the release notes to see what versions have appropriate support. – Craig Ringer Apr 19 '13 at 00:38