2

I have in PostgreSQL tables, each with millions of records and more that one hundred fields.
One of them is a date field, which we filter by this in our queries. The creation of an index for this date field improved the performance of the queries that read an small range of dates, but in big range of dates the performance decreased...

I must prioritize one over the other? The performance in small ranges can be improved without decreasing the big range queries?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Pedro Muñoz
  • 285
  • 3
  • 16

4 Answers4

3

Queries in PostgreSQL cannot be answered just using the information in an index. Whether or not the row is visible, from the perspective of the query that is executing, is stored in the main row itself. So when you add an index to something, and execute a query that uses it, there are two steps involved:

  • Navigate the index to determine which data blocks are used
  • Retrieve those blocks and return the rows that match the query

It is therefore possible that answering a query with an index can take longer than just going directly to the data blocks and fetching the rows. The most common case where this happens is if you are actually grabbing a large portion of the data. Typically if more than 20% of the table is used, it's considered fast to just sequentially access it. Sometimes the planner thinks less than 20% will be accessed, so the index is preferred, but that's not true; that's one way adding an index can slow a query. This may be the situation you're seeing, based on your description--if the large ranges are touching more of the table than the optimizer estimates, using an index can be a net slowdown.

To figure this out, the database collects statistics about each column in each table, to determine whether a particular WHERE condition is selective enough to use an index. The idea is that you need to have saved so many blocks by not reading the whole table that adding the index I/O on top of it is still a net win.

This computation can go wrong, such that you end up doing more I/O than had you just read the table directly, in a couple of cases. The cause of most of them show up if you run the query using EXPLAIN ANALYZE. If the "expected" values versus the "actual" numbers are very different, this can suggest the optimizer had bad statistics on the table. Another possibility is that the optimizer just made a mistake about how selective the query is--it thought it would only return a small number of rows, but it actually returns most of the table. Here, again, better statistics is the normal way to start working on that. If you're on PostgreSQL 8.3 or earlier, the amount of statistics collected is very low by default.

Some workloads end up adjusting the random_page_cost tunable as well, which controls where this index vs. table scan trade-off happens at. That's only something to consider after the stats information is checked though. See Tuning Your PostgreSQL Server for an intro to several things you can adjust here.

Greg Smith
  • 16,965
  • 1
  • 34
  • 27
0

I'd try several things:

  • increase DB cache parameters
  • add the index on that date field
  • redesign/modify the application to work with smaller ranges (althogh this suggestion might seem obvious, it is usually first to be thrown away)
Tudor Constantin
  • 26,330
  • 7
  • 49
  • 72
0

The creation of an index for this date field improved the performance of the queries that read an small range of dates, but in big range of dates the performance decreased...

Try clustering your table using that index. The performance decrease might be due to the entire table getting opened on large ranges. And if so, clustering the table along that index would lead to less disk seeks.

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
0

Two suggestions:

1) Investigate the use of table inheritance for time-series data. For example, create a child table per month and then INDEX the date on each table. PostgreSQL is smart enough to only perform index_scan's on the child tables that have the actual data in the date range. Once the child table is "sealed" because it is a new month, run CLUSTER on the table to sort the data by date.

2) Look at creating a bunch of INDEX's that use WHERE clauses.

Suggestion #1 is going to be the winner long term but will take some work to setup (but will scale/run forever), but suggestion #2 may be a quick interim fix if you have a limited date range that you care about scanning. Remember, you can only use IMMUTABLE functions in your INDEX's WHERE clause.

CREATE INDEX tbl_date_2011_05_idx ON tbl(date) WHERE date >= '2011-05-01' AND date <= '2011-06-01';
Sean
  • 9,888
  • 4
  • 40
  • 43
  • Thanks, but there is a problem, I'm talking about one date field for simplify my question, but realy I use two date fields for filter in diferents querys, one in some querys, one in some others, never the two at the same time. The problem I write about is present in both. One more thing, this two date fields are similar, I mean the diference is little (seconds or miliseconds).Do you have some urls of table inheritance for time-series data? – Pedro Muñoz May 25 '11 at 19:02
  • In scenario #1, how do do inserts or updates on the correct child table? – AngerClown May 25 '11 at 19:03
  • @AngerClown: You use a trigger on a `VIEW` or a `RULE` on your parent table and have the `TRIGGER` function do the `INSERT`/`UPDATE` on the correct child table. Now that you can do `INSERT`s on `VIEW`s, I'd recommend that approach vs `RULE`s, which is the old/hacked up way. – Sean May 25 '11 at 21:35
  • @Martin, if the two date fields are similar, can you do an `INDEX` scan on one `INDEX` and then do a search based on an `INTERVAL` offset? i.e. can you change your schema to be: my_date = base time, my_interval = INTERVAL some offset from my_date that combined, represents your actual second date? If so, then the database/OS would only have one `INDEX` to maintain/keep in memory, which would reduce your memory pressure by 50%. Second to that, look in to periodically `CLUSTER`'ing your data, possibly? – Sean May 25 '11 at 21:40