Postgresql – Tune postgres 9.1 to speedup deletes

linuxperformancepostgresql

I need to tune my postgres db to speed up delete queries. I cant't optimize my db structure yet, so I'm looking for options available on server level for postgres 9.1.

I've seen in older posts the parameter max_fsm_pages, however I can't find the parameter in my postgressql.conf, is this parameter deprecated?

Are there other parameters I can tune?

Best Answer

max_fsm_pages was eliminated in version 8.4. Free space tracking has gotten a lot smarter and more automatic.

Performance of deletes is going to depend on a lot of things.

If there are any foreign keys which reference this table, you should check whether there is an index on the referencing column(s). Without that, each DELETE will cause a scan of the referencing table, to check that you're not causing any rows to be orphaned.
Like other database modifications, it is generally more efficient to group actions into larger transactions where reasonable, since there is overhead to each database transaction.
If you do have many transactions, you might want to consider setting synchronous_commit to off on the connection you're using for that work.
If you are deleting a significant fraction of the table, and you can do this during some maintenance window, you might consider dropping indexes not needed to find the rows to be deleted, and create them again after the mass delete.

Why is it slow?

I would advise to use the query @ypercube provided in combination with the mentioned indexes. But why is the query you had so slow in comparison?

You did not provide your table definition, but I assume from the column names and what you wrote that you have several (large) character type (text or varchar) columns in the table articles:

title, shorttitle, description, markdown, body, idx_fti

I further assume that you are running your database with a locale other than C. Sorting large text columns according to a locale is rather expensive. The relevant thing is the collation. Check your (current) setting for LC_COLLATE:

SHOW LC_COLLATE;

With Postgres 9.1 or later you can pick a collation for evaluating your expressions ad hoc. With PostgreSQL 8.4, however, this is set at cluster creation time and can't be changed later.

We recently had a related question on SO where after much deliberation and testing we found sorting according to a locale to be the major slowdown:

Slow query ordering by a column in a joined table

I expect @ypercube's query to solve that problem radically: No GROUP BY for the long text columns eliminates the expensive sort altogether. Problem solved.

PostgreSQL Index Tuning – BitmapIndexScan vs IndexScan

If I run your example with a cold cache for both queries, then the bitmap index scan really does win out. So in this sense, the planner is getting it correct.

Since you are testing under a perfectly hot cache, the correct thing to do would be to lower the random_page_cost and the seq_page_cost both to zero, and if I do that it does select the index scan you prefer (and at that point, I can't even get it to use the BitMapAnd, no matter what I do to the enable_* parameters).

This is not to say that the planner is doing a good job here. It is vastly underestimating the amount of work that needs to be done to insert a tuple into a bitmap. It assumes it takes 0.1 of a cpu_operator_cost to do so, which seems facially absurd to me.

If you measure your buffer hit/miss rates for this particular query under realistic conditions, rather than under benchmarking conditions, what do they look like? It would be nice to do tests under realistic conditions, but those are very hard to achieve so I can just do all-hot or all-cold and hope it interpolates correctly

Best Answer

Related Solutions

Postgresql – Improve sort performance in PostgreSQL

Why is it slow?

PostgreSQL Index Tuning – BitmapIndexScan vs IndexScan

Related Question