Re: select query performance question

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