Обсуждение: select query performance question
Hi,
subject is the following type of query needed in a function to select data:
SELECT ' 13.04.2009 12:00:00 ' AS zeit,
'M' AS ganglinientyp,
m.zs_nr AS zs,
j_ges,
de_mw_abh_j_lkw(mw_abh) AS j_lkw,
de_mw_abh_v_pkw(mw_abh) AS v_pkw,
de_mw_abh_v_lkw(mw_abh) AS v_lkw,
de_mw_abh_p_bel(mw_abh) AS p_bel
FROM messungen_v_dat_2009_04_13 m
INNER JOIN de_mw w ON w.nr = m.mw_nr
WHERE m.ganglinientyp = 'M'
AND ' 890 ' = m.minute_tag;
explain analyse brings up
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.
Is this query plan near to optimal or are their any serious flaws?
Hello maybe is wrong tip, but your function like de* should be slow. What is time of query without calling these functions? Pavel Stehule 2009/7/27 Thomas Zaksek <zaksek@ptt.uni-due.de>: > Hi, > subject is the following type of query needed in a function to select data: > > SELECT ' 13.04.2009 12:00:00 ' AS zeit, > > > 'M' AS ganglinientyp, > > > m.zs_nr AS zs, > > > j_ges, > > > de_mw_abh_j_lkw(mw_abh) AS j_lkw, > > > de_mw_abh_v_pkw(mw_abh) AS v_pkw, > > > de_mw_abh_v_lkw(mw_abh) AS v_lkw, > > > de_mw_abh_p_bel(mw_abh) AS p_bel > > > FROM messungen_v_dat_2009_04_13 m > > > INNER JOIN de_mw w ON w.nr = m.mw_nr > > > WHERE m.ganglinientyp = 'M' > > AND > ' 890 ' = m.minute_tag; > explain analyse brings up > 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. > Is this query plan near to optimal or are their any serious flaws? > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/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
Thomas Zaksek <zaksek@ptt.uni-due.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
Hi Thomas,
How is 'messungen_v_dat_2009_04_13_gtyp_minute_tag_idx' defined? What is
the row count for the table?
Mike
> Hi,
> subject is the following type of query needed in a function to select
> data:
>
> SELECT ' 13.04.2009 12:00:00 ' AS zeit,
>
> 'M' AS ganglinientyp,
>
> m.zs_nr AS zs,
>
> j_ges,
>
> de_mw_abh_j_lkw(mw_abh) AS j_lkw,
>
> de_mw_abh_v_pkw(mw_abh) AS v_pkw,
>
> de_mw_abh_v_lkw(mw_abh) AS v_lkw,
>
> de_mw_abh_p_bel(mw_abh) AS p_bel
>
> FROM messungen_v_dat_2009_04_13 m
>
> INNER JOIN de_mw w ON w.nr = m.mw_nr
>
> WHERE m.ganglinientyp = 'M'
>
> AND ' 890 ' = m.minute_tag;
> explain analyse brings up
> 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.
> Is this query plan near to optimal or are their any serious flaws?
>
Kevin Grittner wrote:
> Thomas Zaksek <zaksek@ptt.uni-due.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)