如何优化无复合指数的大型 PostgreSQL 表格上的搜索查询?-allqahome-开发者的问答家园

English 中文(简体)

如何优化无复合指数的大型 PostgreSQL 表格上的搜索查询?

原标题：How to Optimize Search Queries on a Large PostgreSQL Table without Compound Indexes?

I have a PostgreSQL table named users which contains between 20 to 30 million records. The table has the following columns: id, tenant_id, name, company, location, and description. I need to perform generic search queries on this table with various combinations of columns. Here is an example of such a query: SELECT * FROM users WHERE name = raju AND company LIKE %kg% AND description LIKE %ght% AND tenant_id = tenant_1234 ; Given that there are many possible combinations of columns in these search queries, the response time is currently very high. To address this, I am considering using compound indexes. However, I am concerned that this will slow down the write operations. My Question: What are some effective strategies to optimize these search queries without significantly impacting the performance of write operations? Any suggestions or best practices would be greatly appreciated.

问题回答

One of the challenging things about SQL and compound indexes is how hard it is to generalize rules about creating those indexes. In almost every application that grows and gains new data, it is necessary to routinely assess which queries are slowing down and create indexes to support them. This may need to be done monthly, or even weekly in a fast-growing app. Setting up the right indexes often is not something a developer can do prior to deployment and then forget. Let s take a look at the query you showed us. WHERE name = raju AND company LIKE %kg% AND description LIKE %ght% AND tenant_id = tenant_1234 A BTREE index on (name, tenant_id) or (tenant_id, name) will help this particular query. BTREE indexes on company or description won t because you use the notorious column LIKE %searchterm% performance antipattern (leading %) there. You can, in PostgreSQL, accelerate the antipattern with a GIN index in place of BTREE, but that s a longer discussion. It may be that other query filter patterns are also helped by the two indexes I mentioned. If either name or tenant_id is selective enough they may help a lot. The insertion overhead of indexes usually gets a bad rap. Unless your insertion rate is very high and you have many indexes, you probably should not worry about this.

上一篇：过滤带有自定义列值的 Acumatica 网格

下一篇：无法连接到服务器数据库系统的启动包的无效长度已被关闭, 收到快速关闭请求( pgadmin postgres docker)

相关问题

我如何将Excel板的数据输入我的Django应用? I m将PosgreSQL数据库作为数据库。

Postgres dump of only parts of tables for a dev snapshot

On production our database is a few hundred gigabytes in size. For development and testing, we need to create snapshots of this database that are functionally equivalent, but which are only 10 or 20 ...

Why isn t psycopg2 executing any of my SQL functions? (IndexError: tuple index out of range)

I ll take the simplest of the SQL functions as an example: CREATE OR REPLACE FUNCTION skater_name_match(INTEGER,VARCHAR) RETURNS BOOL AS $$ SELECT $1 IN (SELECT skaters_skater.competitor_ptr_id ...

采用Psycopg2保持Adhur和Pogresql之间连续联系的最佳/最佳预兆

I m 在当地网络上运行几台机器,在Salgresql8.3处撰写一份申请。

How to join attributes in sql select statement?

I want to join few attributes in select statement as one for example select id, (name + + surname + + age) as info from users this doesn t work, how to do it? I m using postgreSQL.

PostgreSQL , Select from 2 tables, but only the latest element from table 2

Hey, I have 2 tables in PostgreSql: 1 - documents: id, title 2 - updates: id, document_id, date and some data: documents: | 1 | Test Title | updates: | 1 | 1 | 2006-01-01 | | 2 | 1 | 2007-01-01 |...

What text encoding to use?

I need to setup my PostgreSQL DB s text encoding to handle non-American English characters that you d find showing up in languages such as German, Spanish, and French. What character encoding should ...

SQL LIKE condition to check for integer?

I am using a set of SQL LIKE conditions to go through the alphabet and list all items beginning with the appropriate letter, e.g. to get all books where the title starts with the letter "A": SELECT * ...

热门标签

友情链接

Allggapp Alljchome-教程家园 mvfinale.com-影视剧情大结局大全