Severe performance problems for simple query - Mailing list pgsql-performance

Hi folks,

Here is the executive summary:
  * RHEL5 (postgresql 8.1, .conf tweaked for performance [1])
  * 2x Intel E5410 @ 2.33GHz (8 cores), 8GB RAM, 15KRPM SAS disks
  * 4.9 million records in a table (IP address info)
  * composite primary key: primary key(ipFrom, ipTo)
  * ipFrom/ipTo are int8 (see below for full schema info [2])
  * bad performance on queries of the form:
    select * from ipTable where  ipFrom <= val and val <= ipTo

Part of the problem is that most of the time PostgreSQL decides to
use seq scans on the table, resulting in queries taking many seconds
(sometimes 3, 7, 20 sec). We did ANALYZE and enabled statistics, and
that sometimes fixes the problem temporarily, but overnight (without
the database being used), it reverts to seq scans. For example:

perpedes_db=# explain ANALYZE select * from static.ipligenceipaddress where ipfrom <= 2130706433 and 2130706433 <=
ipto;
                                                          QUERY PLAN
       

-------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on ipligenceipaddress  (cost=0.00..139903.80 rows=1209530 width=145) (actual time=1233.628..2100.891 rows=1
loops=1)
   Filter: ((ipfrom <= 2130706433) AND (2130706433 <= ipto))
 Total runtime: 2100.928 ms
(3 rows)



Moreover, even when it is using the index, it is not all that fast:
perpedes_db=# SET enable_seqscan = off;
SET
perpedes_db=# EXPLAIN ANALYZE select * from static.ipligenceipaddress where 3507360727 between ipfrom and ipto;
                                                                         QUERY PLAN
                                    

------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using ipligenceipaddress_pkey on ipligenceipaddress  (cost=0.00..148143.67 rows=806199 width=146) (actual
time=351.316..351.320rows=1 loops=1) 
   Index Cond: ((3507360727::bigint >= ipfrom) AND (3507360727::bigint <= ipto))
 Total runtime: 351.355 ms
(3 rows)


So, my questions are:
  * did we miss any obvious settings?
  * why does it decide all of a sudden to do seq scans?
  * adding a "limit 1" sometimes causes the query to be even slower,
    when in fact it should have helped the DB to return faster, no?
  * in the ideal case, what execution times should I be expecting?
    Is ~400ms reasonable? I would have hoped this to be <40ms...
  * AFAICT, the (ipFrom, ipTo) intervals should be mutually exclusive,
    so the result should be at most one row. Can this info help the
    DB do a faster query? If so, how can I express that?
  * the DB takes tens of minutes to do an ANALYZE on this table,
    which doesn't happen with the default configuration. Any idea
    how I can fix that?

Thank you!

====================================================================
[1] Changes from standard config:
--- /var/lib/pgsql/data/postgresql.conf.orig    2008-03-21 11:51:45.000000000 -0400
+++ /var/lib/pgsql/data/postgresql.conf 2008-03-21 21:04:38.000000000 -0400
@@ -90,19 +90,19 @@

 # - Memory -

-shared_buffers = 1000                  # min 16 or max_connections*2, 8KB each
-#temp_buffers = 1000                   # min 100, 8KB each
-#max_prepared_transactions = 5         # can be 0 or more
+shared_buffers = 50000                 # min 16 or max_connections*2, 8KB each
+temp_buffers = 10000                   # min 100, 8KB each
+max_prepared_transactions = 100                # can be 0 or more
 # note: increasing max_prepared_transactions costs ~600 bytes of shared memory
 # per transaction slot, plus lock space (see max_locks_per_transaction).
-#work_mem = 1024                       # min 64, size in KB
-#maintenance_work_mem = 16384          # min 1024, size in KB
+work_mem = 2048                                # min 64, size in KB
+maintenance_work_mem = 131072          # min 1024, size in KB
 #max_stack_depth = 2048                        # min 100, size in KB

 # - Free Space Map -

-#max_fsm_pages = 20000                 # min max_fsm_relations*16, 6 bytes each
-#max_fsm_relations = 1000              # min 100, ~70 bytes each
+max_fsm_pages = 200000                 # min max_fsm_relations*16, 6 bytes each
+max_fsm_relations = 10000              # min 100, ~70 bytes each

 # - Kernel Resource Usage -

@@ -111,11 +111,11 @@

 # - Cost-Based Vacuum Delay -

-#vacuum_cost_delay = 0                 # 0-1000 milliseconds
-#vacuum_cost_page_hit = 1              # 0-10000 credits
+vacuum_cost_delay = 200                        # 0-1000 milliseconds
+vacuum_cost_page_hit = 6               # 0-10000 credits
 #vacuum_cost_page_miss = 10            # 0-10000 credits
 #vacuum_cost_page_dirty = 20           # 0-10000 credits
-#vacuum_cost_limit = 200               # 0-10000 credits
+vacuum_cost_limit = 100                        # 0-10000 credits

 # - Background writer -

@@ -141,13 +141,13 @@
                                        #   fsync_writethrough
                                        #   open_sync
 #full_page_writes = on                 # recover from partial page writes
-#wal_buffers = 8                       # min 4, 8KB each
+wal_buffers = 128                      # min 4, 8KB each
 #commit_delay = 0                      # range 0-100000, in microseconds
 #commit_siblings = 5                   # range 1-1000

 # - Checkpoints -

-#checkpoint_segments = 3               # in logfile segments, min 1, 16MB each
+checkpoint_segments = 192              # in logfile segments, min 1, 16MB each
 #checkpoint_timeout = 300              # range 30-3600, in seconds
 #checkpoint_warning = 30               # in seconds, 0 is off

@@ -175,12 +175,12 @@

 # - Planner Cost Constants -

-#effective_cache_size = 1000           # typically 8KB each
-#random_page_cost = 4                  # units are one sequential page fetch
+effective_cache_size = 393216          # typically 8KB each
+random_page_cost = 2                   # units are one sequential page fetch
                                        # cost
-#cpu_tuple_cost = 0.01                 # (same)
-#cpu_index_tuple_cost = 0.001          # (same)
-#cpu_operator_cost = 0.0025            # (same)
+cpu_tuple_cost = 0.002                 # (same)
+cpu_index_tuple_cost = 0.0002          # (same)
+cpu_operator_cost = 0.0005             # (same)

 # - Genetic Query Optimizer -

@@ -329,10 +329,10 @@

 # - Query/Index Statistics Collector -

-#stats_start_collector = on
-#stats_command_string = off
-#stats_block_level = off
-#stats_row_level = off
+stats_start_collector = on
+stats_command_string = on
+stats_block_level = on
+stats_row_level = on
 #stats_reset_on_server_start = off


@@ -340,8 +340,8 @@
 # AUTOVACUUM PARAMETERS
 #---------------------------------------------------------------------------

-#autovacuum = off                      # enable autovacuum subprocess?
+autovacuum = on                                # enable autovacuum subprocess?
 #autovacuum_naptime = 60               # time between autovacuum runs, in secs
 #autovacuum_vacuum_threshold = 1000    # min # of tuple updates before
                                        # vacuum
 #autovacuum_analyze_threshold = 500    # min # of tuple updates before
@@ -400,7 +400,7 @@
 #---------------------------------------------------------------------------

 #deadlock_timeout = 1000               # in milliseconds
-#max_locks_per_transaction = 64                # min 10
+max_locks_per_transaction = 512                # min 10
 # note: each lock table slot uses ~220 bytes of shared memory, and there are
 # max_locks_per_transaction * (max_connections + max_prepared_transactions)
 # lock table slots.


[2] Actual schema for the table:
create table ipligenceIpAddress
(
    ipFrom int8 not null default 0,
    ipTo int8 not null default 0,
    countryCode varchar(10) not null,
    countryName varchar(255) not null,
    continentCode varchar(10) not null,
    continentName varchar(255) not null,
    timeZone varchar(10) not null,
    regionCode varchar(10) not null,
    regionName varchar(255) not null,
    owner varchar(255) not null,
    cityName varchar(255) not null,
    countyName varchar(255) not null,
    latitude float8 not null,
    longitude float8 not null,
    createdTS timestamp with time zone default current_timestamp,
    primary key(ipFrom, ipTo)
);

--
Dimi Paun <dimi@lattica.com>
Lattica, Inc.