I have many read-only tables in a Postgres database. All of these tables can be queried using any combination of columns.
What can I do to optimize queries? Is it a good idea to add indexes to all columns to all tables?
2
-
2I'd try to establish where (on which queries) most time is spent, and in the first instance optimize for those.NPE– NPE2011-09-15 16:15:03 +00:00Commented Sep 15, 2011 at 16:15
-
Queries are dynamically generated and the only thing I know is that they are selects with filtering by one or more columns with an orderby clause (and limit/offset). There are no joins.narduk– narduk2011-09-16 19:09:45 +00:00Commented Sep 16, 2011 at 19:09
Add a comment
|
2 Answers
Columns that are used for filtering or joining (or, to a lesser degree, sorting) are of interest for indexing. Columns that are just selected are barely relevant! For the following query only indexes on a and e may be useful:
SELECT a,b,c,d
FROM tbl_a
WHERE a = $some_value
AND e < $other_value;
Here, f and possibly c are candidates, too:
SELECT a,b,c,d
FROM tbl_a
JOIN tbl_b USING (f)
WHERE a = $some_value
AND e < $other_value
ORDER BY c;
After creating indexes (and possibly running ANALYZE or even VACUUM ANALYZE on the table), test to see if they are actually useful with EXPLAIN or EXPLAIN ANALYZE. Also compare execution times with and without the indexes. Deleting and recreating indexes is fast and easy. There are also parameters to experiment with EXPLAIN ANALYZE. The difference may be staggering or nonexistent.
As your tables are read-only, index maintenance is cheap after initial creation. It's merely a question of disk space.
If you really want to know what you are doing, start by reading the docs.
If you don't know what queries to expect ...
Try logging enough queries to find typical use cases. Log queries with the parameter
log_statement = allfor that. Or just log slow queries usinglog_min_duration_statement.Create indexes that might be useful and check the statistics after some time to see what actually gets used. PostgreSQL has a whole infrastructure in place for monitoring statistics. One convenient way to study statistics (and many other tasks) is pgAdmin where you can chose your table / function / index and get all the data on the "statistics" tab in the object browser (main window).
Proceed as described above to see if indexes in use actually speed things up.
If the query planner should chose to use one or more of your indexes but to no or adverse effect then something is probably wrong with your setup and you need to study the basics of performance optimization: vacuum, analyze, cost parameters, memory usage, ...
answered Sep 15, 2011 at 23:16
Erwin Brandstetter
668k159 gold badges1.2k silver badges1.3k bronze badges
9 Comments
narduk
Queries are dynamically generated and the only thing I know is that they are selects with filtering by one or more columns with an orderby clause (and limit/offset). There are no joins. Do you consider adding indexes on all columns would be good practice in this case?
Erwin Brandstetter
Just adding indexes on all columns may or may not help. Indexes do have costs, especially for heavily written tables. Less so for read-only tables. I added a paragraph to my answer above.
narduk
Unfortunately, I won't be able to find typical use cases since I won't have access to the server once the project is finished. I wanted to do everything I could to optimize it in the development stage. Anyway, a lot of useful tips. Thanks.
KyelJmD
Why is it that only indexes on a and e may be useful?
Erwin Brandstetter
@KyelJmD: That's true for the first example and the explanation is given in bold letters:
a and e ar used to filter the result. (WHERE a = $some_value AND e < $other_value. |
If you have filtering by more columns indexes may help but not too much. Also indexes may not help for small tables.
First search for "postgresql tuning" - you will find usefull information.
If database can fit in memory - buy enough RAM. If database can not fit in memory - SSD will help. If this is not enough and database is read only - run 2, 3 or more servers. Or partition database (in the best case to fit in memory of each server).
Even if queries are generated I think they will not be random. Monitor database for slow queries and improve only them.
