6

Question

How can I make the query described in this post faster, in particular by making PostgreSQL using the available RAM? - Note that I have tried to configure effective_cache_size and shared_buffers appropriately. See below.

Background

I have to regularly join a ~260 million row table (coreg_master) with new data that comes in. I've partitioned the table to allow each partition to fit in RAM. I do have appropriate indexes set up too of course. However, when joining the partitioned tables separately against the other (much smaller) tables, it's doing fully random IO on the disk. This is due to a nested loop index scan on the large table, which is really slow as we don't have a great disk setup.

I'd expect it to use all available RAM to cache the large partitioned table instead, which I understand should be done by the Linux kernel/filesystem itself. But it still doesn't load the table into RAM, though it would fit. I guess that's because the access pattern isn't sequential and thus doesn't trigger caching? I have no idea. Query plan and configuration parameters below.

Table Structure

This is one of the partitions of coreg_master, my large table. The partitioned tables are named coreg_a, coreg_b, etc.

\d coreg_a
                                   Table "public.coreg_a"
   Column    |       Type        |                         Modifiers                         
-------------+-------------------+-----------------------------------------------------------
 id          | integer           | not null default nextval('coreg_master_id_seq'::regclass)
 first_name  | character varying | 
 last_name   | character varying | 
 phone       | character varying | 
 city        | character varying | 
 zip         | integer           | 
 address     | character varying | 
 dob         | date              | 
 ip          | character varying | 
 source      | character varying | 
 gender      | character varying | 
 state       | character varying | 
 record_date | date              | 
 email       | character varying | 
Indexes:
    "coreg_a_name" btree (lower(first_name::text), lower(last_name::text))
Check constraints:
    "coreg_a_first_name_check" CHECK (first_name::text >= 'a'::text AND first_name::text < 'b'::text)
Inherits: coreg_master

The following is a partition of table appendable_24, an example of the table being joined with coreg_master. It is also partitioned the same way as coreg_master, so actually coreg_a is joined with appendable_24_a, etc, one at a time.

\d appendable_24_a
       Table "public.appendable_24_a"
   Column   |       Type        | Modifiers 
------------+-------------------+-----------
 line_num   | integer           | not null
 first_name | character varying | 
 last_name  | character varying | 
 address    | character varying | 
 state      | character varying | 
 zip        | integer           | 
Indexes:
    "appendable_24_a_name_index" btree (lower(first_name::text), lower(last_name::text))
Check constraints:
    "appendable_24_a_first_name_check" CHECK (first_name::text >= 'a'::text AND first_name::text < 'b'::text)
Inherits: appendable_24

Query & EXPLAIN ANALYZE

Below is the output of explain analyze for the smallest of the joins (tables are partitioned according to the first letter of the first_name column), as it doesn't take ages. However, the query plan is the same for all the joins on each partition, so it should be representative of the larger joins as well (note, i did ANALYZE the tables, and the total time was actually 20 seconds, but faster here as the result was cached):

explain analyze SELECT
          coreg_x.phone,
          coreg_x.email,
          coreg_x.record_date,
          appendable_24_x.line_num
        FROM appendable_24_x INNER JOIN coreg_x ON
          lower(appendable_24_x.first_name) = lower(coreg_x.first_name) AND
          lower(appendable_24_x.last_name) = lower(coreg_x.last_name) AND
          (coreg_x.phone IS NOT NULL OR coreg_x.email IS NOT NULL) AND
          similarity(lower(appendable_24_x.address), lower(coreg_x.address)) > 0.7
      ; 
                                                                                       QUE
RY PLAN 

------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
-----
 Nested Loop  (cost=0.01..640.49 rows=875 width=39) (actual time=9.990..53.839 rows=29 loo
ps=1)
   Join Filter: (similarity(lower((appendable_24_x.address)::text), lower((coreg_x.address
)::text)) > 0.7::double precision)
   ->  Seq Scan on appendable_24_x  (cost=0.00..1.80 rows=80 width=34) (actual time=0.009.
.0.111 rows=80 loops=1)
   ->  Index Scan using coreg_x_name on coreg_x  (cost=0.01..7.95 rows=1 width=64) (actual
 time=0.024..0.137 rows=44 loops=80)
         Index Cond: ((lower((coreg_x.first_name)::text) = lower((appendable_24_x.first_na
me)::text)) AND (lower((coreg_x.last_name)::text) = lower((appendable_24_x.last_name)::tex
t)))
         Filter: ((coreg_x.phone IS NOT NULL) OR (coreg_x.email IS NOT NULL))
 Total runtime: 53.950 ms
(7 rows)

Some stats, configuration parameters and other data

  • PostgreSQL Version: 8.4.4
  • OS: CentOS release 5.5 (Final)
  • Filesystem: ext3
  • Total available RAM: 8GB
  • shared_buffers = 2GB
  • effective_cache_size = 7200MB
  • full runtime configuration through show all: http://pastie.org/1159746
  • largest partitioned coreg table (coreg_j) size: ~4900MB
  • corresponding number of rows: ~32 million
  • corresponding (first_name, last_name) index size: ~1000MB
  • second partitioned table (appendable_24_j) size: ~1800kB
  • rows in appendable_24_j: ~25,000

1 Answer 1

2
+50

There's only enough ram to cache a fraction of your db, and as the query plan you posted shows, previously accessed data/portions of the index in question are indeed cached. (Postgres does not cache query results).

53 ms runtime isn't too shabby, and I'm not sure 20 sec on uncached data means that PG picked a bad query plan either. After all, just the index in question is 1GB large, but it would be interesting to see analyze output for a slow query.

You could try adjusting planner costs to see if that has any effect on worst-case performance, if that's your issue.

You might also want to increase your maintentance_work_mem a bit, even if that's in no way related.

5
  • Thanks for the response. While there's not enough memory to cache the entire DB, there is enough memory (8GB) to store the entire two tables being joined (5GB). But I'm seeing slow random disk IO instead, which is the issue. 53 ms runtime would be amazing, it's taking many hours to join all the partitions in the manner I described. This is due the the random disk IO. If postgresql would just load each entire partition table into memory, it would be blazing.
    – ehsanul
    Sep 20, 2010 at 15:58
  • I'm not sure your assumptions about random io are on the mark. The query plan above looks sound to me, and you say it runs in seconds. I don't see how that would turn into hours no matter how many partitions you have. Please show us the real query, add output from explain if you can't do explain analyze.
    – eevar
    Sep 20, 2010 at 20:37
  • 20 seconds is for the small partition, coreg_x, being joined with another small table, appendable_24_x. However, coreg_j and appendable_24_j are both much, much larger and that query does much longer, and similarly for coreg_a joined with appendable_24_a. The query plan is identical for all, posted above. I'll try running getting the "explain analyze" results for it though, if it's helpful. "Many hours" refers to running all 26 (a-z) join queries in series, from coreg_a to coreg_z. I'm pretty sure about random IO since iostat showed very low read speeds with full disk utilization.
    – ehsanul
    Sep 20, 2010 at 23:08
  • "previously accessed data/portions of the index in question are indeed cached." - This is true, but I'm not really going to use that cache. It would be better if, instead of a lot of random IO, the full table were loaded into memory and index lookups were performed using that. Perhaps running a SELECT * from coreg_j; would accomplish this effect by having linux cache the table, but that seems extraneous and silly.
    – ehsanul
    Sep 20, 2010 at 23:15
  • 2
    Yes, I agree there's random IO, but I'm not sure that's a problem. According to your planner costs, pg thinks it's the cheapest option. Did you try adjusting them? Start a psql session, explain your query, "set random_page_cost = 10;", then explain it again. Keep raising it until the query plan changes, then explain analyze. Does it actually end up running faster? -- If it does, your random_page_cost was too low, or pg has bad statistics for the small table.
    – eevar
    Sep 21, 2010 at 13:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .