Full Text Search PostgreSQL

แชร์
ฝัง
  • เผยแพร่เมื่อ 22 ธ.ค. 2024

ความคิดเห็น • 129

  • @PreetamBhosle
    @PreetamBhosle 5 ปีที่แล้ว +139

    You can do half queries using 'isl:*' , this will return everything that starts with 'isl'. If you want a multiword search, add in between the words i.e 'sea mon:*'. This will return the row with 'Sea Monster'

    • @bawad
      @bawad  5 ปีที่แล้ว +9

      Oh awesome

    • @herbertpimentel
      @herbertpimentel 5 ปีที่แล้ว +1

      Hey man, you look to understand very well about this... do you have any video to share with us ?

    • @sungatae
      @sungatae 4 ปีที่แล้ว +1

      What impact does :* have on performance?

    • @benargee
      @benargee 3 ปีที่แล้ว +1

      @@sungatae he explained how to check in the video. Explain analyze.

    • @dedygunanto
      @dedygunanto 3 ปีที่แล้ว +2

      what if i want to search 'island' just with 'land', i tried with '*:land' is not working and generate error message?

  • @jayadevanm8386
    @jayadevanm8386 4 ปีที่แล้ว +28

    Thank you for the detailed step-by-step session. PostgreSQL 12, released later, added generated columns. So we no longer need a trigger to keep the search column updated.

  • @gabriel_export
    @gabriel_export 3 ปีที่แล้ว +19

    So much clearer than the postgresql docs. My head was spinning reading all the technical details of how it works. THANKS

  • @Pan-ux3bq
    @Pan-ux3bq 3 ปีที่แล้ว +1

    This is the best and the simplest explanation of postgresql FTS I could find on internet

  • @arkadyzelensky7655
    @arkadyzelensky7655 4 ปีที่แล้ว +1

    I searched for a full-text search explanation and just clicked on the first video on TH-cam. And this was such luck!
    Very clear and interesting explanation. Thank you a lot for doing this!

  • @amavumkal
    @amavumkal 3 ปีที่แล้ว +5

    That was very helpful. I love how you started very basic and then got into the complex queries. Thanks so much!

  • @schriker17
    @schriker17 4 ปีที่แล้ว +3

    Works great! I've just tested it with 125k rows, and results are close enough so i can omit running elasticksearch like database!

  • @christiangyaban9666
    @christiangyaban9666 5 ปีที่แล้ว +14

    Ben...finally my request have been granted...👍u rock

  • @lakshyarajdash
    @lakshyarajdash 2 ปีที่แล้ว

    Great explanation.. saved a lot of time that I wasted searching over the internet.

  • @birham07
    @birham07 3 ปีที่แล้ว +1

    I like how you followed start simple finish master approach. Thank you for your awesome contents once again!

  • @lemonphi
    @lemonphi 5 ปีที่แล้ว +2

    Great video! Not sure if it was already answered, but regarding your last question of 'how to query for half a word' - this seems to be possible with 'to_tsquery' but not 'plainto_tsquery', you can use it like 'to_tsquery('blah:*')'. Greets, Bernd

    • @bawad
      @bawad  5 ปีที่แล้ว

      thanks

  • @eminem2
    @eminem2 5 ปีที่แล้ว +1

    Ok, you know how people say that they want to do something and don't know how and in the very same day you upload the exact solution? You did it again! By the way at the moment I am using Redis's RediSearch module. It's super fast, I mean hyper fast. But I have some data in Hasura too, so for some data it's overkill to put in Redis. So will see your solution in action.

    • @bawad
      @bawad  5 ปีที่แล้ว +1

      I've been meaning to try out the search module for Redis, that sounds sweet

  • @pablo_brianese
    @pablo_brianese 4 ปีที่แล้ว

    I didn't expect to find a video from you on this topic. I am lucky!

  • @michaelhearmon9965
    @michaelhearmon9965 4 ปีที่แล้ว

    I come from a T-SQL background, but codewars use Postgresql.
    This was really interesting to learn, thanks for the upload

  • @usernamegaro
    @usernamegaro 4 ปีที่แล้ว +1

    i really appreciate your effort for explaining in a simple way this powerful function, thanks a lot! ;)

  • @barclaysd
    @barclaysd 5 ปีที่แล้ว +5

    Such a great tutorial! Thanks Ben!

  • @SeriousM4x
    @SeriousM4x 2 ปีที่แล้ว

    i was just looking up this topic, clicked on the video and half way though realised its from ben

  • @sethmoeckel6160
    @sethmoeckel6160 2 ปีที่แล้ว +2

    Postgres Generated columns would be really useful for generating search documents as changes are made. Its also a bit simpler than triggers.

    • @stevezelaznik5872
      @stevezelaznik5872 2 ปีที่แล้ว +1

      It is, as long as the input data for true search index lives on the same table. Once you join tables together to make a search index, a generated column doesn’t work.

  • @rubinskyzlatrovich6731
    @rubinskyzlatrovich6731 5 ปีที่แล้ว +2

    💪🏻💪🏻 2 of the list ✅. Dude, nice video! Seems like the video will do great! congrats and 🙏🏻thank you

  • @michaeldausmann6066
    @michaeldausmann6066 4 ปีที่แล้ว

    Great video, the docs on this are pretty dense so this was helpful. It still blows me away how good postgresql is and how much you get essentially for free.

  • @MohanChawla-jy2wc
    @MohanChawla-jy2wc 11 หลายเดือนก่อน

    Very clear explanation. Thank You so much!

  • @abelhuaranca4785
    @abelhuaranca4785 2 ปีที่แล้ว +1

    EXCELLENT LESSON, PERFECT, SUBLIME, THANKS THANKS BEN AWAD!!!! A HUUUUUUUUUUUUUUUUGE THANKS!!!!! :D

  • @sufiyaanrajput7533
    @sufiyaanrajput7533 4 ปีที่แล้ว +6

    The tutorial was really great and has helped me get started. However in my use case I have multiple tables to search through and I have achieved it without indexing. How would you index columns from different tables for a single FTS query?

    • @stevezelaznik5872
      @stevezelaznik5872 2 ปีที่แล้ว +1

      The solution is no longer as elegant, but you create a new table which combines the values you want to search on. Let's say you have a "users" table and a "blog_posts" table.
      CREATE TABLE searchable_blog_posts (
      id SERIAL,
      user_id INTEGER,
      blog_post_id INTEGER,
      search_index tsvector
      )
      CREATE INDEX index_searchable_blog_posts_search_index ON search_index USING GIN(search_index);
      Then you can populate this "searchable_blog_posts" table using the information you want to be searchable. Then you use triggers to update this `searchable_blog_posts` table whenever either a change is made to the "users" table or the "blog_posts" table.

  • @lldadb664
    @lldadb664 ปีที่แล้ว

    Right to the point with great examples. Thanks!

  • @mh5854
    @mh5854 ปีที่แล้ว +2

    use plainto_tsquery instead of to_tsquery, because to_tsquery does not escape the sql letters like ', \, !, so the query can be easily injected

  • @mokshchadha9151
    @mokshchadha9151 3 หลายเดือนก่อน

    this is was very helpful and step by step reallly helped.

  • @dennistennis5622
    @dennistennis5622 5 ปีที่แล้ว

    Great video again Ben!

  • @sanskarkaazi3830
    @sanskarkaazi3830 2 ปีที่แล้ว

    At 15:18 , the variable "document_with_weights" got the datatype "any" which might be available in this PL but let's say if you had to do this with java or c# .. what datatype do you use for the variable?

  • @saeedrezaalipourfard1902
    @saeedrezaalipourfard1902 4 ปีที่แล้ว +1

    Great tutorial! Thanks.

  • @froylanvargas6902
    @froylanvargas6902 4 ปีที่แล้ว

    Excellent tutorial here! Thanks.

  • @alex14779
    @alex14779 5 ปีที่แล้ว +4

    what about performance when you re-create index on every update?

    • @bawad
      @bawad  5 ปีที่แล้ว

      no idea

    • @JasonKim1
      @JasonKim1 5 ปีที่แล้ว +4

      I would recommend against that. Indexing is costly. On a table with 1 million rows, you can expect indexing to take roughly 5 to 10 seconds. Also some indexing locks the table. Index your table with a delayed job that gets performed daily or hourly.

    • @victorxu9634
      @victorxu9634 5 ปีที่แล้ว

      you rebuild the GIN/GiST index for that row when the row is updated. More info here www.postgresql.org/docs/9.1/textsearch-indexes.html

  • @tunedmystic1
    @tunedmystic1 5 ปีที่แล้ว

    Awesome! Great video Ben! subbed 👍

    • @bawad
      @bawad  5 ปีที่แล้ว

      Welcome :)

  • @aufula
    @aufula 3 ปีที่แล้ว

    I create a MATERIALIZED VIEW and a combined GIN index on it to prevent adding those additional columns.

  • @bmdolan
    @bmdolan 4 ปีที่แล้ว

    This is a GREAT tutorial! Thanks.

  • @sojun2642
    @sojun2642 5 ปีที่แล้ว +1

    Awesome!,
    I followed the video and got good results, I also solved the problem of leyword is "isl" to search for "Island", just add "isl: *",
    but i want to search:
    when I enter the keyword "land", I want to get "island",
    I tried with prefixes like: "*: keyword: *", but this is not a good shot and an err sytanx is printed,
    What do I do with this problem?
    Any help is appreciated,
    thanks

  • @willcowan7678
    @willcowan7678 4 ปีที่แล้ว

    Awesome tutorial, thank you

  • @dedygunanto
    @dedygunanto 3 ปีที่แล้ว

    Awesome tutorial!!

  • @armaan8350
    @armaan8350 3 ปีที่แล้ว

    If a table have child table then how a text can be search from parent table as well as child table. Please help me. I stuck in my project

  • @antoniosousa4178
    @antoniosousa4178 3 ปีที่แล้ว

    You are the man.

  • @SergiiStarodubtsev
    @SergiiStarodubtsev 3 ปีที่แล้ว

    I noticed that using tvector make search 2 times slower comparing to "and (like '%abc%' or like '%bcd%'), on top of this tvector performance is not stable, sometimes its say 6 seconds, sometimes 11 seconds to get the response

  • @JacksonWelch
    @JacksonWelch 3 ปีที่แล้ว +1

    Great video, super helpful. Wonder how this would scale with a really large database.

  • @ВикторАлександрович-д5г
    @ВикторАлександрович-д5г ปีที่แล้ว

    if we need to use search on 2 tables then how to build an index in this case.
    pls help

  • @ackava
    @ackava 3 ปีที่แล้ว

    Can't you create a computed column for tsvector instead of creating trigger?

  • @eduardmart1237
    @eduardmart1237 2 ปีที่แล้ว

    How do you add these indexes automatically on insert or update?

  • @vishwas22khare
    @vishwas22khare 3 ปีที่แล้ว

    Man how you connect posters full text search with your saffron website please please please tell me .

  • @vichitrakumar4452
    @vichitrakumar4452 4 ปีที่แล้ว

    Great tutorial!

  • @PeterCulka
    @PeterCulka 4 ปีที่แล้ว

    Thanks for this. Now what about accentuated chars? Latin2 etc ?

  • @alooooooola
    @alooooooola 3 ปีที่แล้ว

    postgres will cache query so that benchmark is clearly not prove anything. The only difference is strategy the database plan to perform. At 5:50 you showed 2 query the first is much slower than the second. However, look at the strategy it is the same (seq scan which is the worst) but the result is cached so it was faster. You need to re thinking about it

  • @asifhaider5638
    @asifhaider5638 4 ปีที่แล้ว

    did you use javascript to see the probable result in your website or just prosgres ...if you did..how you do it ...can you help me

  • @leandrogripp1251
    @leandrogripp1251 3 ปีที่แล้ว

    A little late, but does anyone know how can i implement something like the initial example, that searches a bunch of keywords? The behavior I want is to get every result that matches at least one of the keywords, but ordered by number of matches (and if possible, still considering the given weights for the fields, as a match in the title is still more valuable than a match in the description)

  • @kythinhnguyen5474
    @kythinhnguyen5474 2 ปีที่แล้ว

    very usefull, thank you.

  • @LawZist
    @LawZist 5 ปีที่แล้ว

    great video!

  • @rafaelrequenatroncos2771
    @rafaelrequenatroncos2771 3 ปีที่แล้ว

    Can you do full text with incomplete words?

  • @TehLostGamers
    @TehLostGamers 4 ปีที่แล้ว

    Amazing tutorial good job, just one small issue your trigger does not update new income rows it should have an update statement in the trigger instead of a :=
    e.g.
    BEGIN
    UPDATE table_name SET
    document_with_weights = setWeight(.........)
    WHERE id = NEW.id;
    return NEW
    END

  • @waterydan
    @waterydan 4 ปีที่แล้ว

    Just wondering how I can search across multiple tables? Do I need to concatenate all tables into a new one and do the full text search there?

    • @geralt36
      @geralt36 3 ปีที่แล้ว

      how about creating a view of all those tables and having a tsvector column of all the required columns in that same view

  • @DrPanesar
    @DrPanesar 5 ปีที่แล้ว

    hi Ben thanks for the video. Is there a way to do a partial match to a word with this type of search setup? So Noo would find Noodles?

    • @bawad
      @bawad  5 ปีที่แล้ว

      I think so, but I haven't tried to do it myself

  • @me_sami05
    @me_sami05 4 ปีที่แล้ว

    Could you please help me to understand the difference between Full Text Search PostgreSQL and Elasticsearch?

    • @neneodonkor
      @neneodonkor 2 ปีที่แล้ว

      PostgreSQL is database that uses tables while Elasticsearch uses JSON. The latter is focused on search queries and so it's faster. In my opinion, it is geared towards enterprise stuff.

  • @merlin2600
    @merlin2600 5 ปีที่แล้ว

    Awesome video !
    The issue that it won't work for "isl" but well "islands" because it's an English word variation is really annoying when searching for names: searching an artist "Martin" won't match "Martina".
    If anyone has a pointer about a way to achieve that, I'm interested.

    • @theacid1
      @theacid1 2 ปีที่แล้ว

      You can add a '':*' in the search query. Example with 'isla': plainto_tsquery('isla:*') will match all words prefixed with 'isla'

  • @sushilbansalk
    @sushilbansalk 5 ปีที่แล้ว

    there is a directive available on Typeorm: @Index({ fulltext: true })
    Not sure what it does and how it works.

    • @bawad
      @bawad  5 ปีที่แล้ว

      I didn't know that existed, I'll have to go check it out

    • @stevereid636
      @stevereid636 5 ปีที่แล้ว

      Did you ever get round to checking this out?

    • @bawad
      @bawad  5 ปีที่แล้ว

      Nope, haven't done another project with fulltext search yet

  • @jeudesprits440
    @jeudesprits440 5 ปีที่แล้ว

    Ben Awad, what u say about FoundationDB?

    • @bawad
      @bawad  5 ปีที่แล้ว

      never tried it

  • @fenkssoehas4513
    @fenkssoehas4513 5 ปีที่แล้ว

    Could please tell me what IDE that you've been used? thx

    • @bawad
      @bawad  5 ปีที่แล้ว +2

      vscode

  • @probleemsupport3119
    @probleemsupport3119 3 ปีที่แล้ว

    wich software do u use there?

    • @dejfcold
      @dejfcold 2 ปีที่แล้ว

      It's JetBrains' DataGrip for the DB stuff and VS Code for the JS stuff

  • @ArtemSutulov
    @ArtemSutulov 5 ปีที่แล้ว

    Thanks a lot

  • @CardinalHijack
    @CardinalHijack 4 ปีที่แล้ว

    Hey Ben, do you know how these results compare to using typeorm find or findOne?

    • @bawad
      @bawad  4 ปีที่แล้ว

      you'll want to use find or findOne first if that supports your use case

  • @preppedchef
    @preppedchef 5 ปีที่แล้ว

    Is this possible utilizing knex?

    • @bawad
      @bawad  5 ปีที่แล้ว

      yeah this isn't specific to typeorm, just postgres

  • @cunningham.s_law
    @cunningham.s_law 5 ปีที่แล้ว

    can you make videos on elastic search?

    • @bawad
      @bawad  5 ปีที่แล้ว +1

      sure

  • @CoryMcaboy
    @CoryMcaboy 5 ปีที่แล้ว

    What editor of that?

    • @bawad
      @bawad  5 ปีที่แล้ว

      www.jetbrains.com/datagrip/

    • @danieldosen5260
      @danieldosen5260 5 ปีที่แล้ว

      I'm a big an of datagrip - just know that you can tweak your sql syntax formatting. I'm not a big fan of their default editor formatting rules.

    • @bawad
      @bawad  5 ปีที่แล้ว

      oh cool

    • @maximsobolevskiy5595
      @maximsobolevskiy5595 5 ปีที่แล้ว

      @@danieldosen5260 But can you configure the style you need? If not, please, file a feature request in our tracker: youtrack.jetbrains.com/issues/DBE

  • @septinmulatsihrezki2823
    @septinmulatsihrezki2823 5 ปีที่แล้ว

    nice video

  • @NexoLP
    @NexoLP 5 ปีที่แล้ว

    First thank you for this great video!
    But I'm still wondering how it works to search not just for one word. I want to search with more words as one coherent term, so that you compare for example the search term "highway robber battlefield" with the data in your data base. Does anyone have a solution for this?

    • @lemonphi
      @lemonphi 5 ปีที่แล้ว

      you can use operators like &, | and !

  • @alexsantiago2797
    @alexsantiago2797 4 ปีที่แล้ว

    I love you. Is there anything you haven't covered?

  • @WernerRaath
    @WernerRaath 4 ปีที่แล้ว +4

    I would definitely recommend this video, which builds on what you've said: th-cam.com/video/c8IrUHV70KQ/w-d-xo.html

  • @sreevishal2223
    @sreevishal2223 4 ปีที่แล้ว

    Your awesome (y)..

  • @San_OO7
    @San_OO7 2 ปีที่แล้ว

    MySQL vs PSQL please

  • @pajeetsingh
    @pajeetsingh 3 ปีที่แล้ว

    You did not consider time to create a new column.

    • @tim.bogdanov
      @tim.bogdanov ปีที่แล้ว

      Exactly what im facing right now. I assume you mean adding more columns down the road to search on right?

  • @vieto4847
    @vieto4847 5 ปีที่แล้ว

    thank u for this video ! But i want to get all records in table and still use *doc..* @@ plainto_tsquery(:query) . So what do i put in "query" ?

    • @bawad
      @bawad  5 ปีที่แล้ว +1

      Why do you need to use tsquery if you want all the records?

  • @sharam1337
    @sharam1337 5 ปีที่แล้ว +1

    every recipe is from julie right now.

    • @bawad
      @bawad  5 ปีที่แล้ว

      In the video that's Julie's account
      Or your looking at the sample cookbook which she made

  • @paraavcs2125
    @paraavcs2125 5 ปีที่แล้ว

    Can you use Synonym search with PostgreSql or Elasticsearch on next video?
    Here an example.
    medium.com/@lucasmagnum/elasticsearch-setting-up-a-synonyms-search-facea907ef92

  • @andresmontoya7852
    @andresmontoya7852 5 ปีที่แล้ว

    I did an issue in the typeorm GitHub, it is about how to do an fulltext query with MySQL and typeorm github.com/typeorm/typeorm/issues/3191 I don't know if it is better than what you did, but I want to know your thoughts about it :)

    • @bawad
      @bawad  5 ปีที่แล้ว

      I've never tried to do that with MySQL so I'm not sure how it compares

    • @andresmontoya7852
      @andresmontoya7852 5 ปีที่แล้ว

      @@bawad you can run it over docker and try it

  • @obedm503
    @obedm503 5 ปีที่แล้ว

    would be cool if you made a similar video but using pg_trgm instead of vectors
    also sent a twitter dm about this

  • @kerodfresenbetgebremedhin1881
    @kerodfresenbetgebremedhin1881 2 ปีที่แล้ว

    trigrams

  • @japroz
    @japroz 3 ปีที่แล้ว

    Hhhiiiii

  • @theacid1
    @theacid1 2 ปีที่แล้ว +1

    There is a rather big mistake in the search query at around 17:00. You used english stemming for creating the vector but not in the search query. Problem is that for example the word 'Training' will get stemmed to 'train' but now if a user searches for 'training' the search won't find any results because they do not match. You have to use 'english' stemming also in the query like this: document_with_weights @@ plainto_tsquery('english', :query)"

  • @Spongeoel
    @Spongeoel 4 ปีที่แล้ว

    great video!!