Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a large table of objects (15M rows +), for which I want to query for outdated field. PostgreSQL (9.0.8)

I want to divide the query by millions, for scalability & concurrency purposes, and I want to fetch all data with the updated_at field with a date of a few days ago.

I have tried many indexes, and queries, on a million ids, and I can't seem to get performance under 100seconds. (with heroku's Ronin hardware).

I am looking for suggestions I haven't tried to make this as efficient as possible.

TRY #1

 EXPLAIN ANALYZE SELECT count(*) FROM objects WHERE (date(updated_at)) < (date(now())-7) AND id >= 5000001 AND id < 6000001;
 INDEX USED : (date(updated_at),id)
 268578.934 ms

TRY #2

 EXPLAIN ANALYZE SELECT count(*) FROM objects WHERE ((date(now()) - (date(updated_at)) > 7)) AND id >= 5000001 AND id < 6000001;
 INDEX USED : primary key
 335555.144 ms

TRY #3

 EXPLAIN ANALYZE SELECT count(*) FROM objects WHERE (date(updated_at)) < (date(now())-7) AND id/1000000 = 5;
 INDEX USED : (date(updated_at),(id/1000000))
 243427.042 ms

TRY #4

 EXPLAIN ANALYZE SELECT count(*) FROM objects WHERE (date(updated_at)) < (date(now())-7) AND id/1000000 = 5 AND updated_at IS NOT NULL;
 INDEX USED : (date(updated_at),(id/1000000)) WHERE updated_at IS NOT NULL 
 706714.812 ms

TRY #5 (for a single month of outdated data)

 EXPLAIN ANALYZE SELECT count(*) FROM objects WHERE (EXTRACT(MONTH from date(updated_at)) = 8) AND id/1000000 = 5 ;
 INDEX USED : (EXTRACT(MONTH from date(updated_at)),(id/1000000))
 107241.472 ms

TRY #6

 EXPLAIN ANALYZE SELECT count(*) FROM objects WHERE (date(updated_at)) < (date(now())-7) AND id/1000000 = 5 ;
 INDEX USED : ( (id/1000000 ) ASC ,updated_at DESC NULLS LAST)
 106842.395 ms

TRY #7

 EXPLAIN ANALYZE SELECT count(*) FROM objects WHERE id/1000000 = 5 and (date(updated_at)) < (date(now())-7)  ;
 INDEX USED : ( (id/1000000 ) ASC ,date(updated_at) DESC NULLS LAST);
 100732.049 ms
 Second try: 87280.728 ms 
     http://explain.depesz.com/s/DQP

TRY #8

 EXPLAIN ANALYZE SELECT count(*) FROM objects WHERE (date(updated_at)) < (date(now())-7) AND id/1000000 = 5 AND updated_at IS NOT NULL;
 INDEX USED :  ( (id/1000000 ) ASC ,date(updated_at) ASC NULLS LAST);
 129133.022 ms

TRY #9 (with partial index, as per Erwin's suggestion)

 EXPLAIN ANALYZE SELECT count(*) FROM objects WHERE id BETWEEN 5000000 AND 5999999 AND (date(updated_at)) < '2012-10-23'::date;
 INDEX USED : (date(updated_at) DESC NULLS LAST) WHERE id BETWEEN 5000000 AND 6000000 AND date(updated_at) < '2012-10-23'::date ;
 73861.047 ms
 http://explain.depesz.com/s/p9A

TRY #10 (CLUSTER, as per Erwin's suggestion).

 CREATE INDEX ix_8 on objects ( (id/1000000 ) ASC ,date(updated_at) DESC NULLS LAST);
 CLUSTER entities USING ix_8;
 EXPLAIN ANALYZE SELECT count(*) FROM objects WHERE id/1000000 = 5 and (date(updated_at)) < (date(now())-7) ;
 4745.595 ms

 EXPLAIN ANALYZE SELECT count(*) FROM objects WHERE id/1000000 = 10 and (date(updated_at)) < (date(now())-7) ;
 17573.639 ms

 ==> This solution seems to be the winning one. I'll have to test thoroughly to verify counterimpacts everywhere in my application.

============================ UPDATE (db settings)

 select name,min_val, max_val, boot_val from pg_settings;

              name               |  min_val  |   max_val    |     boot_val      
---------------------------------+-----------+--------------+-------------------
 allow_system_table_mods         |           |              | off
 application_name                |           |              | 
 archive_command                 |           |              | 
 archive_mode                    |           |              | off
  archive_timeout                 | 0         | 2147483647   | 0
 array_nulls                     |           |              | on
 authentication_timeout          | 1         | 600          | 60
 autovacuum                      |           |              | on
 autovacuum_analyze_scale_factor | 0         | 100          | 0.1
 autovacuum_analyze_threshold    | 0         | 2147483647   | 50
 autovacuum_freeze_max_age       | 100000000 | 2000000000   | 200000000
 autovacuum_max_workers          | 1         | 536870911    | 3
 autovacuum_naptime              | 1         | 2147483      | 60
 autovacuum_vacuum_cost_delay    | -1        | 100          | 20
 autovacuum_vacuum_cost_limit    | -1        | 10000        | -1
 autovacuum_vacuum_scale_factor  | 0         | 100          | 0.2
 autovacuum_vacuum_threshold     | 0         | 2147483647   | 50
 backslash_quote                 |           |              | safe_encoding
 bgwriter_delay                  | 10        | 10000        | 200
 bgwriter_lru_maxpages           | 0         | 1000         | 100
 bgwriter_lru_multiplier         | 0         | 10           | 2
 block_size                      | 8192      | 8192         | 8192
 bonjour                         |           |              | off
 bonjour_name                    |           |              | 
 bytea_output                    |           |              | hex
 check_function_bodies           |           |              | on
 checkpoint_completion_target    | 0         | 1            | 0.5
 checkpoint_segments             | 1         | 2147483647   | 3
 checkpoint_timeout              | 30        | 3600         | 300
 checkpoint_warning              | 0         | 2147483647   | 30
 client_encoding                 |           |              | SQL_ASCII
 client_min_messages             |           |              | notice
 commit_delay                    | 0         | 100000       | 0
 commit_siblings                 | 1         | 1000         | 5
 constraint_exclusion            |           |              | partition
 cpu_index_tuple_cost            | 0         | 1.79769e+308 | 0.005
 cpu_operator_cost               | 0         | 1.79769e+308 | 0.0025
 cpu_tuple_cost                  | 0         | 1.79769e+308 | 0.01
 cursor_tuple_fraction           | 0         | 1            | 0.1
 custom_variable_classes         |           |              | 
 DateStyle                       |           |              | ISO, MDY
 db_user_namespace               |           |              | off
 deadlock_timeout                | 1         | 2147483      | 1000
 debug_assertions                |           |              | off
 debug_pretty_print              |           |              | on
 debug_print_parse               |           |              | off
 debug_print_plan                |           |              | off
 debug_print_rewritten           |           |              | off
 default_statistics_target       | 1         | 10000        | 100
 default_tablespace              |           |              | 
 default_text_search_config      |           |              | pg_catalog.simple
 default_transaction_isolation   |           |              | read committed
 default_transaction_read_only   |           |              | off
 default_with_oids               |           |              | off
 effective_cache_size            | 1         | 2147483647   | 16384
 effective_io_concurrency        | 0         | 1000         | 1
 enable_bitmapscan               |           |              | on
 enable_hashagg                  |           |              | on
 enable_hashjoin                 |           |              | on
 enable_indexscan                |           |              | on
 enable_material                 |           |              | on
 enable_mergejoin                |           |              | on
 enable_nestloop                 |           |              | on
 enable_seqscan                  |           |              | on
 enable_sort                     |           |              | on
 enable_tidscan                  |           |              | on
 escape_string_warning           |           |              | on
 extra_float_digits              | -15       | 3            | 0
 from_collapse_limit             | 1         | 2147483647   | 8
 fsync                           |           |              | on
 full_page_writes                |           |              | on
 geqo                            |           |              | on
 geqo_effort                     | 1         | 10           | 5
  geqo_generations                | 0         | 2147483647   | 0
  geqo_pool_size                  | 0         | 2147483647   | 0
  geqo_seed                       | 0         | 1            | 0
  geqo_selection_bias             | 1.5       | 2            | 2
  geqo_threshold                  | 2         | 2147483647   | 12
  gin_fuzzy_search_limit          | 0         | 2147483647   | 0
  hot_standby                     |           |              | off
  ignore_system_indexes           |           |              | off
  integer_datetimes               |           |              | on
  IntervalStyle                   |           |              | postgres
  join_collapse_limit             | 1         | 2147483647   | 8
  krb_caseins_users               |           |              | off
  krb_srvname                     |           |              | postgres
  lc_collate                      |           |              | C
  lc_ctype                        |           |              | C
  lc_messages                     |           |              |
  lc_monetary                     |           |              | C
  lc_numeric                      |           |              | C
  lc_time                         |           |              | C
  listen_addresses                |           |              | localhost
  lo_compat_privileges            |           |              | off
  local_preload_libraries         |           |              |
  log_autovacuum_min_duration     | -1        | 2147483      | -1
  log_checkpoints                 |           |              | off
  log_connections                 |           |              | off
  log_destination                 |           |              | stderr
  log_disconnections              |           |              | off
  log_duration                    |           |              | off
  log_error_verbosity             |           |              | default
  log_executor_stats              |           |              | off
  log_hostname                    |           |              | off
  log_line_prefix                 |           |              |
  log_lock_waits                  |           |              | off
  log_min_duration_statement      | -1        | 2147483      | -1
  log_min_error_statement         |           |              | error
  log_min_messages                |           |              | warning
  log_parser_stats                |           |              | off
  log_planner_stats               |           |              | off
  log_rotation_age                | 0         | 35791394     | 1440
  log_rotation_size               | 0         | 2097151      | 10240
  log_statement                   |           |              | none
  log_statement_stats             |           |              | off
  log_temp_files                  | -1        | 2147483647   | -1
  log_timezone                    |           |              | UNKNOWN
  log_truncate_on_rotation        |           |              | off
  logging_collector               |           |              | off
  maintenance_work_mem            | 1024      | 2097151      | 16384
  max_connections                 | 1         | 536870911    | 100
  max_files_per_process           | 25        | 2147483647   | 1000
  max_function_args               | 100       | 100          | 100
  max_identifier_length           | 63        | 63           | 63
  max_index_keys                  | 32        | 32           | 32
  max_locks_per_transaction       | 10        | 2147483647   | 64
  max_prepared_transactions       | 0         | 536870911    | 0
  max_stack_depth                 | 100       | 2097151      | 100
  max_standby_archive_delay       | -1        | 2147483      | 30000
  max_standby_streaming_delay     | -1        | 2147483      | 30000
  max_wal_senders                 | 0         | 536870911    | 0
  password_encryption             |           |              | on
  port                            | 1         | 65535        | 5432
  post_auth_delay                 | 0         | 2147483647   | 0
  pre_auth_delay                  | 0         | 60           | 0
  random_page_cost                | 0         | 1.79769e+308 | 4
  search_path                     |           |              | "$user",public
  segment_size                    | 131072    | 131072       | 131072
  seq_page_cost                   | 0         | 1.79769e+308 | 1
  server_encoding                 |           |              | SQL_ASCII
  server_version                  |           |              | 9.0.8
  server_version_num              | 90008     | 90008        | 90008
  session_replication_role        |           |              | origin
  shared_buffers                  | 16        | 1073741823   | 1024
  silent_mode                     |           |              | off
  sql_inheritance                 |           |              | on
  ssl                             |           |              | off
  ssl_renegotiation_limit         | 0         | 2097151      | 524288
  standard_conforming_strings     |           |              | off
  statement_timeout               | 0         | 2147483647   | 0
  superuser_reserved_connections  | 0         | 536870911    | 3
  synchronize_seqscans            |           |              | on
  synchronous_commit              |           |              | on
  syslog_facility                 |           |              | local0
  syslog_ident                    |           |              | postgres
  tcp_keepalives_count            | 0         | 2147483647   | 0
  tcp_keepalives_idle             | 0         | 2147483647   | 0
  tcp_keepalives_interval         | 0         | 2147483647   | 0
  temp_buffers                    | 100       | 1073741823   | 1024
  temp_tablespaces                |           |              |
  TimeZone                        |           |              | UNKNOWN
  timezone_abbreviations          |           |              | UNKNOWN
  trace_notify                    |           |              | off
  trace_recovery_messages         |           |              | log
  trace_sort                      |           |              | off
  track_activities                |           |              | on
  track_activity_query_size       | 100       | 102400       | 1024
  track_counts                    |           |              | on
  track_functions                 |           |              | none
  transaction_isolation           |           |              |
  transaction_read_only           |           |              | off
  transform_null_equals           |           |              | off
  unix_socket_group               |           |              |
  unix_socket_permissions         | 0         | 511          | 511
  update_process_title            |           |              | on
  vacuum_cost_delay               | 0         | 100          | 0
  vacuum_cost_limit               | 1         | 10000        | 200
  vacuum_cost_page_dirty          | 0         | 10000        | 20
  vacuum_cost_page_hit            | 0         | 10000        | 1
  vacuum_cost_page_miss           | 0         | 10000        | 10
  vacuum_defer_cleanup_age        | 0         | 1000000      | 0
  vacuum_freeze_min_age           | 0         | 1000000000   | 50000000
  vacuum_freeze_table_age         | 0         | 2000000000   | 150000000
  wal_block_size                  | 8192      | 8192         | 8192
  wal_buffers                     | 4         | 2147483647   | 8
  wal_keep_segments               | 0         | 2147483647   | 0
  wal_level                       |           |              | minimal
  wal_segment_size                | 2048      | 2048         | 2048
  wal_sender_delay                | 1         | 10000        | 200
  wal_sync_method                 |           |              | fdatasync
  wal_writer_delay                | 1         | 10000        | 200
  work_mem                        | 64        | 2097151      | 1024
  xmlbinary                       |           |              | base64
  xmloption                       |           |              | content
  zero_damaged_pages              |           |              | off
  (195 rows) 
share|improve this question
    
Maybe you could give some additional information: table structure, index definition, expected proportion of outdated dates, row_width, tuning parameters? The solution will probably be either tuning, or partial indices. –  wildplasser Oct 29 '12 at 22:33
    
The table structure has ~20-30 columns, a few foreign keys integers, string, text, booleans. The index definition is in the posting above next to INDEX USEd. (I posted the 8 indexes used by the queries). I have a few more indexes for quicker update and selects for my application. Finally, I am using a cloud DB, and I haven't change anything. I am wondering mostly if my index definition is as good as it can gets before getting to those optimization. Nevertheless, I'll update with the info. –  xlash Oct 29 '12 at 22:43
    
Ok. Thanks Thanks. (BTW Most gurus read SQL faster than natural language text ;-) I's also less ambiguous... BTW2: I am not a guru, but I least I have that habit. BTW3 15M rows is not huge. Only a few thousand pages, given moderate rowsize. –  wildplasser Oct 29 '12 at 22:45
1  
If I understand correctly you can add the context field to the pg_settings query to find out if you can change that particular constant: select name,context,min_val, max_val, boot_val from pg_settings;. If context=='user', you could use SET xxx = val; to set a parameter before the query. for xxx= {effective_cache_size, work_mem, random_page_cost} are the usual suspects. –  wildplasser Oct 29 '12 at 23:43
    
Thanks for showing your Pg version. While it's good that you've posted real query timings, what'd be really good would be to show the DDL (as wildplasser asked) and the actual query plan for at least some of the queries. Paste the explain analyze output to explain.depesz.com and link to it in the answer. –  Craig Ringer Oct 30 '12 at 0:06
show 5 more comments

migrated from stackoverflow.com Oct 31 '12 at 1:30

This question came from our site for professional and enthusiast programmers.

1 Answer

First off, can it be?? You write:

I want to fetch all data with the updated_at field with a date of a few days ago.

But your WHERE condition is:

(date(updated_at)) < (date(now())-7)

Shouldn't that be >?


Indexes

For optimal performance, you could ...

  • partition your indexes
  • exclude irrelevant rows from the indexes
  • automatically recreate indexes at off-hours with updated WHERE clause.

Your indexes could look like:

CREATE INDEX objects_id_updated_at_idx (updated_at::date DESC NULLS LAST)
WHERE  id BETWEEN 0 AND 999999
AND    updated_at > '2012-10-01 0:0'::timestamp  -- some minimum date

CREATE INDEX objects_id_updated_at_idx (updated_at::date DESC NULLS LAST)
WHERE  id BETWEEN 1000000 AND 1999999
AND    updated_at > '2012-10-01 0:0'::timestamp  -- some minimum date

...

The second condition excludes irrelevant rows from the index right away, which should make it smaller and faster - depending on your actual data distribution. I am assuming here, that you want newer rows according to my leading comment. Else, revert the direction of the clause.
It also automatically excludes updated_at IS NOT NULL - which you also seem to allow. Of course, a date is relative, the usefulness of the index deteriorates over time. You have to recreate (at off hours) with updated WHERE clause from periodically.

To further optimize, you could use CLUSTER like we mentioned in the comments. But you need a full index for that. Doesn't work with a partial index. You would create temporarily:

CREATE INDEX objects_full_idx (id/1000000, updated_at::date DESC NULLS LAST);

This form of the full index matches the series of partial indexes.

CLUSTER objects USING objects_full_idx;
ANALYZE objects;

This will take a while, since the table is rewritten physically. It is also effectively a VACUUM FULL. It needs an exclusive write lock on the table, so do it at off-hours - provided you can afford that at all.

Then drop the index again right away. This is a one-time effect. I would at least try this once to see how much your queries benefit from it. The effect deteriorates with subsequent write operations. You could repeat this procedure at off hours it you see a considerable effect.

If your table receives a lot of write operations, you have to weigh cost and benefit for this step. You should also consider setting a FILLFACTOR lower than 100 in this case. Do that before you CLUSTER.

Query

SELECT count(*)
FROM   objects
WHERE  id BETWEEN 0 AND 999999  -- match conditions of partial index!
AND    updated_at > '2012-10-01 0:0'::timestamp
AND    updated_at::date > (now()::date - 7)

More

This closely related answer on dba.SE uses a more advanced technique of index partitioning for a similar problem. Among other things it provides example code for automatic index (re-)creation.

This may be out of reach, but if you can upgrade to PostgreSQL 9.2 it has several features you would profit from. Index-only scans alone would make it worth your while.

Make sure that Autovacuum is running properly. The huge gain by CLUSTER you have reported may be due in part to the implicit VACUUM FULL that you get from CLUSTER. Maybe this is set up by Heroku automatically, not sure.
The settings in your question look good. So that's probably not an issue here and CLUSTER was really that effective.

share|improve this answer
    
So far so good ! With Partial index : 73861.047ms I'll continue the CLUSTER test later on. Thanks –  xlash Oct 30 '12 at 16:53
    
And for now, Heroku doesn't support 9.2 yet. I can upgrade to 9.1 only. –  xlash Oct 30 '12 at 18:06
    
@xlash: Note that I had a missing 0 in the full index. –  Erwin Brandstetter Oct 30 '12 at 21:42
    
With cluster, performance is 10x better. See results above (update of main question). I'll have extended tests to perform to verify other impacts –  xlash Oct 31 '12 at 2:40
    
@xlash: You can still use the partial indexes (and matching queries) after CLUSTER - which I designed to agree with the partial indexes). Should be faster than using the full index, which is only needed for CLUSTER. Then all of your queries should be equally fast (like the faster one in your try #10), given they return an equal number of rows. Also, I added a hint concerning autovacuum. –  Erwin Brandstetter Oct 31 '12 at 19:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.