Re: select query performance question

Kevin Grittner wrote:
> Thomas Zaksek <zaksek@ptt.uni-due.de> wrote:
>
>
>> Is this query plan near to optimal or are their any serious flaws?
>>
>
> I didn't see any problem with the query, but with the information
> provided, we can't really tell if you need to reconfigure something,
> or maybe add an index.
>
> The plan generated for the query is doing an index scan and on one
> table and randomly accessing related rows in another, with an average
> time per result row of about 4ms.  Either you've got *really* fast
> drives or you're getting some benefit from cache.  Some obvious
> questions:
>
> What version of PostgreSQL is this?
>
> What OS is the server on?
>
> What does the server hardware look like?  (RAM, drive array, etc.)
>
> What are the non-default lines in the postgresql.conf file?
>
> What are the definitions of these two tables?  How many rows?
>
> -Kevin
>
Postgresql 8.3

Freebsd 7.2

A HP Server with  Dual Opteron, 8GB Ram and a RAID 5 SCSI System

\d+ de_mw;
                               Table "de_mw"
 Column  |   Type   |                     Modifiers
| Description
---------+----------+----------------------------------------------------+-------------
 nr      | integer  | not null default nextval('de_mw_nr_seq'::regclass) |
 j_ges   | smallint |                                                    |
 mw_abh  | integer  |                                                    |
 mw_test | bit(19)  |                                                    |
Indexes:
    "de_mw_pkey" PRIMARY KEY, btree (nr)
    "de_mw_j_ges_key" UNIQUE, btree (j_ges, mw_abh, mw_test)
    "de_nw_nr_idx" btree (nr)
Has OIDs: no


\d+ messungen_v_dat_2009_04_13
     Table "messungen_v_dat_2009_04_13"
    Column     |     Type     | Modifiers | Description
---------------+--------------+-----------+-------------
 ganglinientyp | character(1) | not null  |
 minute_tag    | smallint     | not null  |
 zs_nr         | integer      | not null  |
 mw_nr         | integer      |           |
Indexes:
    "messungen_v_dat_2009_04_13_pkey" PRIMARY KEY, btree (ganglinientyp,
minute_tag, zs_nr)
    "messungen_v_dat_2009_04_13_gtyp_minute_tag_idx" btree
(ganglinientyp, minute_tag)
    "messungen_v_dat_2009_04_13_gtyp_minute_tag_zs_nr_idx" btree
(ganglinientyp, minute_tag, zs_nr)
    "messungen_v_dat_2009_04_13_minute_tag_idx" btree (minute_tag)
Foreign-key constraints:
    "messungen_v_dat_2009_04_13_mw_nr_fkey" FOREIGN KEY (mw_nr)
REFERENCES de_mw(nr)
    "messungen_v_dat_2009_04_13_zs_nr_fkey" FOREIGN KEY (zs_nr)
REFERENCES de_zs(zs)
Inherits: messungen_v_dat
Has OIDs: no

select count(*) from messungen_v_dat_2009_04_13
traffic_nrw_0_4_0-# ;
  count
---------
 6480685
(1 row)


traffic_nrw_0_4_0=# select count(*) from de_mw;
  count
----------
 23853134
(1 row)