Re: select query performance question

Kevin Grittner wrote:
> Thomas Zaksek <zaksek(at)ptt(dot)uni-due(dot)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)