Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Improving Postgres text search speed

charityapi.org

Post reply on HN
Print view View original

41–50 of 59 posts

Re: Improving Postgres text search speed

#41

Post by silvestrov »

zasdffaa wrote: post #30

> A key gotcha that tripped us up: when querying with a list of primary keys, Postgres will not return the records in the same order as the IDs provided in a “where … in” clause. That's frightening they don't know that. So, burn this into your minds: no ordering is guaranteed in SQL unless an ORDER BY is applied to it (and any ordering in a subquery is lost). Even if it seems to work, it will fail. No guarantees on o…

Sometimes I wish for a 'debug' version of PG which adds an "order by random()" to all queries (or appends a "random()" clause to an existing "order by" clause).

Or even better: have it as a connection/session parameter.

Re: Improving Postgres text search speed

#42

Post by zasdffaa »

silvestrov wrote: post #41
zasdffaa wrote: post #30

> A key gotcha that tripped us up: when querying with a list of primary keys, Postgres will not return the records in the same order as the IDs provided in a “where … in” clause. That's frightening they don't know that. So, burn this into your minds: no ordering is guaranteed in SQL unless an ORDER BY is applied to it (and any ordering in a subquery is lost). Even if it seems to work, it will fail. No guarantees on o…

Sometimes I wish for a 'debug' version of PG which adds an "order by random()" to all queries (or appends a "random()" clause to an existing "order by" clause). Or even better: have it as a connection/session parameter.

> Sometimes I wish for a 'debug' version of PG which adds an "order by random()" to all queries

interesting! I like it.

> or appends a "random()" clause to an existing "order by" clause

That one I don't understand. If the Order By is there, why randomise it? I guess

   order by p, q, r, random()
then it sort of allows you to find out if it's been ordered sufficiently for your needs - is that it?

Re: Improving Postgres text search speed

#43

Post by irjustin »

zasdffaa wrote: post #30

> A key gotcha that tripped us up: when querying with a list of primary keys, Postgres will not return the records in the same order as the IDs provided in a “where … in” clause. That's frightening they don't know that. So, burn this into your minds: no ordering is guaranteed in SQL unless an ORDER BY is applied to it (and any ordering in a subquery is lost). Even if it seems to work, it will fail. No guarantees on o…

> That's frightening they don't know that

It's a common problem when moving from Mysql. We personally ran into that shortly after switching to Postgres, but seems like something they should've discovered a lot earlier for sure.

Re: Improving Postgres text search speed

#44

Post by rubyist5eva »

zasdffaa wrote: post #42
silvestrov wrote: post #41

Earlier quoted context omitted.

Sometimes I wish for a 'debug' version of PG which adds an "order by random()" to all queries (or appends a "random()" clause to an existing "order by" clause). Or even better: have it as a connection/session parameter.

> Sometimes I wish for a 'debug' version of PG which adds an "order by random()" to all queries interesting! I like it. > or appends a "random()" clause to an existing "order by" clause That one I don't understand. If the Order By is there, why randomise it? I guess order by p, q, r, random() then it sort of allows you to find out if it's been ordered sufficiently for your needs - is that it?

Sufficient yes because if, for example, you’re simply sorting by a value that’s shared/duplicated like enums values, the ordering of the ordered groups is basically up to the query planner and can change (one query may use an index while another does a sequential scan, for example). The most common use case where I’ve seen this be significant is offset based pagination where the query plan actually changes depending on which page you’re on. You’ll either miss records or even get the a few rows back from the previous page because of the different ordering chosen by the planner/optimizer.

Re: Improving Postgres text search speed

#45

Post by rubyist5eva »

jrochkind1 wrote: post #13

How can querying for PK's than doing a second query on the FK be faster than a join that's semantically equivalent? Postgres optimizer gone terribly wrong or something? Or am I misunderstanding what's going on? It would never have occurred to me to even try this, I would have assumed postgres would do as well as could be done at what looks like a very standard join. So I guess that's a lesson not to make assumptions?…

I’ve seen it happen when people forget to index the foreign key columns, it also may be faster on one to many joins if you need to use DISTINCT to remove duplicate rows (instead of joining on a specific subquery that eliminates the duplication implicitly).

Re: Improving Postgres text search speed

#46

Post by bastawhiz »

jrochkind1 wrote: post #13

How can querying for PK's than doing a second query on the FK be faster than a join that's semantically equivalent? Postgres optimizer gone terribly wrong or something? Or am I misunderstanding what's going on? It would never have occurred to me to even try this, I would have assumed postgres would do as well as could be done at what looks like a very standard join. So I guess that's a lesson not to make assumptions?…

You're right. I had the same thought.

Frankly, it's such a simple case that I'm hesitant to pin the problem on Postgres. I'm now inclined to believe that the ORM/query builder they're using (which I'm not familiar with) is generating a query that does something unusual. I'm also having a hard time understanding the code, so it may just be a bad query in the first place. If the generated SQL was shown, it would be much easier to look into.

Generally speaking, if you're just joining some data onto the results of an expensive operation, it's exactly the same as doing a second query, just without the round trip and overhead.

The lesson here is to always look at EXPLAIN ANALYZE any time you're doing Postgres optimization work. There's a wealth of useful information that will often point you to your mistake. Databases are quite often extremely good at doing common-sense operations well.

Re: Improving Postgres text search speed

#47

Post by jonatron »

zasdffaa wrote: post #30

> A key gotcha that tripped us up: when querying with a list of primary keys, Postgres will not return the records in the same order as the IDs provided in a “where … in” clause. That's frightening they don't know that. So, burn this into your minds: no ordering is guaranteed in SQL unless an ORDER BY is applied to it (and any ordering in a subquery is lost). Even if it seems to work, it will fail. No guarantees on o…

I know this now, but when I didn't and when Postgres returned the same order thousands of times, then changed for no apparent reason, it made fixing flaky tests very tricky.

Re: Improving Postgres text search speed

#48

Post by maverwa »

I remember achieving some quite good results when implementing a simple „fuzzyesque“ search for some B2B e-commerce system a few years back, but what hit us hard was the german dictionary used for stemming for the ts_vector index. The original one coming with psql did not support german compound words correctly and the „fix“ was to import a 3rd party one. I learned the hard ways that this comes at the cost of Postgres loading the dictionary every time a new connection uses the index. Running a simple, small php app that could not reuse/pool connections, every search query done was coming in on a clean connection and hit a >1s fee for having to load the dict.

iirc pgbouncer saved the day there.

Otherwise it worked fine.

Re: Improving Postgres text search speed

#49

Post by pbronez »

kabes wrote: post #26
vosper wrote: post #16

PGSync might be useful for those who don't mind also running Elasticsearch https://github.com/toluaina/pgsync

In that case you may also want to take a look at https://github.com/zombodb/zombodb

Oh that’s super neat! Looks like a more elegant way to link Postgres and Elasticsearch:

“From a technical perspective, ZomboDB is a 100% native Postgres extension that implements Postgres' Index Access Method API. As a native Postgres index type, ZomboDB allows you to CREATE INDEX ... USING zombodb on your existing Postgres tables. At that point, ZomboDB takes over and fully manages the remote Elasticsearch index and guarantees transactionally-correct text-search query results.”

Re: Improving Postgres text search speed

#50

Post by eric4smith »

asah wrote: post #24
eric4smith wrote: post #6

PostgreSQL text search is awesome - for English and Roman type languages. But Asian languages such as Thai, Japanese, Korean, etc are not going to work at all. PostgreSQL is weird about joins. Joining on certain columns could be super fast but others dog slow. And this can flip depending on size of table and this index Cardinality. That’s why it’s important on databases that grow quickly to check the performance of e…

Support for i18n: https://www.google.com/search?q=pgroonga

Yeah we use it.

It’s however a database within a database. PostgreSQL does not natively text search on these kinds of languages.

Post reply on HN