Thread: Advice for optimizing queries using Large Tables

Hi,

I hope someone out there has experience with large tables in Postgres.

I'm working with a table containing over 65 million records in Postgres v 7.1.3. The machine is a dual-processor Athlon MP1900 (Tyan Tiger board) with 3GB of PC2100 DDR RAM, and 3-80GB IBM 120GXP hard drives configured in a software RAID 0 Array running under RedHat Linux v. 7.2. Queries don't seem to be running as fast as "they should".

One of the columns (of type INTEGER) in this table holds a 4-digit value. The values are all within the range 1930-2001. I've indexed this column using the statements:

    CREATE INDEX value_idx ON table (value);

    VACUUM ANALYZE table;

This query:

    SELECT count(*) FROM table WHERE value=1999;

takes approximately 45 minutes to execute, and returns a count of approx 2.2 million records. My "instinct" is that this is much too slow for a query of an indexed column running on this hardware. Here is the EXPLAIN output:

    EXPLAIN SELECT count(*) FROM table WHERE value=1999;

    NOTICE: QUERY PLAN:

    Aggregate (cost=477861.60..477861.60 rows=1 width=0)

        -> Index Scan using value_idx on table (cost=0.00..477553.70 rows=123157 width=0)

    EXPLAIN

Here’s the table schema:

Table "table"

Attribute  | Type          | Modifier
-----------+---------------+----------
col01      | character(9)  |
col02      | character(15) |
col03      | character(15) |
col04      | character(1)  |
col05      | character(15) |
col06      | character(15) |
col07      | character(15) |
col08      | character(15) |
col09      | character(1)  |
col10      | integer       |
col11      | integer       |
col12      | integer       |
col13      | integer       |
col14      | integer       |
value      | integer       |

Indices: value_idx

I’ve set SHMMAX and SHMMALL to 2075721728 in /proc/sys/kernel/, and in postgresql.conf I’ve set the parameters to:

sort_mem = 262144 # sorting memory (in bytes) = 256 MB

shared_buffers = 249036 # this is 1.5GB/8192

fsync = true

Does anyone have any advice for optimizing the SELECT query listed above? Is this as fast as Postgresql will perform? Any good pointers on working with large tables in Postgres?

I appreciate your advice.

Best regards,