Re: Advice for optimizing queries using Large Tables

On Sat, 9 Mar 2002, Shaun Grannis wrote:

> 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".


Have you considered moving to a SCSI setup?

>     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.

As it was suggested you may want to consider going to 7.2

> 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       |


> Does anyone have any advice for optimizing the SELECT query listed above?

Another "optimization"/trick we do here is to split tables into the most
needed info and the least needed info. If you have a part of the data
which is used often you put it in the "main" table. If you have data which
is big and not used often you put it in a second table. In our case we
have a few tables where the size of the data which is not used often can
be from 2 to 4 times the size of the data used often. This helps a lot
with joins and sequential scans.


>Is this as fast as Postgresql will perform? Any good pointers on working
>with large tables in Postgres?

I would suspect this is not as fast as PostgreSQL can perform. Although my
data set is not 60+ million records to do a somewhat complex  aggregate +
join of 5+ million records takes on the 30 minutes range.