Jeff Janes:
That is such a beautiful little trick. I made a table with just ids, and a query for it reads almost 10 times less buffers (as reported by explain analyze buffers), and sure enough, after another reboot, query executes about 10 times faster.
I'm not doing anything special with those results. I have a main table "core" with various information about entries. Some entries have plaintexts attached and those are stored in the additional table "fulldata". fulldata's primary key refers to core's primary key and to do a fulltext search filtering results using core's other fields I have to retrieve primary keys from fulldata.
We tried many different ways to join rows from fulldata and core for that query, and ended up with something along the lines of:
where core.id_iu in (with ids as(select id_iu from fulldata where <fulltext condition here>) select * from ids) and <other core conditions here>
It was just as fast/slow as table joins and subqueries but always used fulltext index no matter what planner had in mind.
I'll be sure to play around with fake immutable function, and I think it might be even worth it to add the index to core instead of thin table.
Thanks!That is such a beautiful little trick. I made a table with just ids, and a query for it reads almost 10 times less buffers (as reported by explain analyze buffers), and sure enough, after another reboot, query executes about 10 times faster.
I'm not doing anything special with those results. I have a main table "core" with various information about entries. Some entries have plaintexts attached and those are stored in the additional table "fulldata". fulldata's primary key refers to core's primary key and to do a fulltext search filtering results using core's other fields I have to retrieve primary keys from fulldata.
We tried many different ways to join rows from fulldata and core for that query, and ended up with something along the lines of:
where core.id_iu in (with ids as(select id_iu from fulldata where <fulltext condition here>) select * from ids) and <other core conditions here>
It was just as fast/slow as table joins and subqueries but always used fulltext index no matter what planner had in mind.
I'll be sure to play around with fake immutable function, and I think it might be even worth it to add the index to core instead of thin table.
Jim Nasby:
Well, it's 32 bytes per row vs only god knows how many bytes per row since every row contains a tsvector value (although since practice shows 10 times less buffers read, it's probably somewhere around 320 bytes on average?).
