Skip site navigation (1) Skip section navigation (2)

Re: select query performance question

From: Thomas Zaksek <zaksek(at)ptt(dot)uni-due(dot)de>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: select query performance question
Date: 2009-07-29 10:37:28
Message-ID: 4A702668.7040600@ptt.uni-due.de (view raw or flat)
Thread:
Lists: pgsql-performance
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)




In response to

pgsql-performance by date

Next:From: Matthew WakelingDate: 2009-07-29 11:09:43
Subject: Re: hyperthreaded cpu still an issue in 8.4?
Previous:From: Greg SmithDate: 2009-07-29 06:35:22
Subject: Re: hyperthreaded cpu still an issue in 8.4?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group