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

Re: select query performance question

From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: Thomas Zaksek <zaksek(at)ptt(dot)uni-due(dot)de>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: select query performance question
Date: 2009-07-27 14:43:26
Message-ID: alpine.DEB.2.00.0907271538130.19493@aragorn.flymine.org (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, 27 Jul 2009, Thomas Zaksek wrote:
> Nested Loop  (cost=0.00..66344.47 rows=4750 width=10)
>              (actual time=134.160..19574.228 rows=4148 loops=1)
>  ->  Index Scan using messungen_v_dat_2009_04_13_gtyp_minute_tag_idx on messungen_v_dat_2009_04_13 m
>              (cost=0.00..10749.14 rows=4750 width=8)
>              (actual time=64.681..284.732 rows=4148 loops=1)
>        Index Cond: ((ganglinientyp = 'M'::bpchar) AND (891::smallint = > minute_tag))
>  ->  Index Scan using de_nw_nr_idx on de_mw w
>              (cost=0.00..10.69 rows=1 width=10)
>              (actual time=4.545..4.549 rows=1 loops=4148)
>        Index Cond: (w.nr = m.mw_nr)
> Total runtime: 19590.078 ms
>
> Seems quite slow to me.

Not necessarily. Consider that your query is fetching 4148 different rows 
in an index scan. That means that your index finds 4148 row locations on 
disc, and 4148 separate disc operations need to be performed to fetch 
them. If you divide the time taken by that number, you get:

19590.078 / 4148 = 4.7 (milliseconds per seek)

Which seems quite good actually. That's as fast as hard drives work.

Now if the data was in cache, it would be a completely different story - I 
would expect the whole query to complete within a few milliseconds.

Matthew

-- 
 And why do I do it that way? Because I wish to remain sane. Um, actually,
 maybe I should just say I don't want to be any worse than I already am.
         - Computer Science Lecturer

In response to

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2009-07-27 14:43:38
Subject: Re: select query performance question
Previous:From: Віталій ТимчишинDate: 2009-07-27 14:33:32
Subject: Re: Can Postgres use an INDEX over an OR?

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