Kevin Grittner wrote:
Thomas Zaksek <zaksek@xxxxxxxxxxxxxx> 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)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
