Re: What determines the cost of an index scan?

Christian

i would suggest ensuring results will be pre-ordered (according to the column to be merged)

anyone?
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.

> Date: Sun, 4 Jan 2009 11:35:51 +0100
> From: cs(at)deriva(dot)de
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] What determines the cost of an index scan?
>
> Hi list,
> I have experienced the following situation: A join between two tables
> (one with ~900k rows, the other with ~1600k rows) takes about 20 sec on
> our productive database. I have created two tables in our test database
> with the same data, but with fewer fields. (I have omitted several
> fields that do not participate in the join.) If I try the join in our
> test database it takes about 8 sec. Both queries have the same query plan:
>
> prod=# explain analyze select 1 from dtng."Z_UL" inner join
> dtng."Z_BARRIER" using ("ISIN", "ID_NOTATION");
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------------------------
> Merge Join (cost=0.00..131201.15 rows=39376 width=0) (actual
> time=0.198..16086.185 rows=1652076 loops=1)
> Merge Cond: ((("Z_UL"."ISIN")::bpchar = ("Z_BARRIER"."ISIN")::bpchar)
> AND ("Z_UL"."ID_NOTATION" = "Z_BARRIER"."ID_NOTATION"))
> -> Index Scan using "Z_UL_pkey" on "Z_UL" (cost=0.00..34806.57
> rows=897841 width=20) (actual time=0.075..1743.396 rows=897841 loops=1)
> -> Index Scan using "Z_BARRIER_ISIN_ID_NOTATION_key" on "Z_BARRIER"
> (cost=0.00..83255.17 rows=1652076 width=20) (actual time=0.076..3389.676
> rows=1652076 loops=1)
> Total runtime: 18123.042 ms
>
> test=# explain analyze select 1 from table1 inner join table2 using
> (key1, key2);
>
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------------
> Merge Join (cost=0.00..82443.05 rows=36158 width=0) (actual
> time=0.092..8036.490 rows=1652076 loops=1)
> Merge Cond: ((table1.key1 = table2.key1) AND (table1.key2 = table2.key2))
> -> Index Scan using table1_pkey on table1 (cost=0.00..22719.56
> rows=897841 width=20) (actual time=0.026..845.916 rows=897841 loops=1)
> -> Index Scan using table2_key1_key2_key on table2
> (cost=0.00..46638.20 rows=1652076 width=20) (actual time=0.049..1843.047
> rows=1652076 loops=1)
> Total runtime: 8460.956 ms
>
> No disk io occurs in either server, so I guess that the whole data is
> already in memory. Both servers are idle. Both use the same PostgreSQL
> version (8.2.9). Both servers are 64bit machines. However, the servers
> have different CPUs and memory: The production server has 4 Dual-Core
> AMD Opteron 8214 processors (2.2 GHz) and 16 GB memory, the test server
> has 2 Dual-Core Intel Xeon 5130 processors (2.0 GHz) and 8 GB memory. I
> have not yet done a CPU and memory benchmark, but this is my next step.
>
> Where does this difference come from? Pure cpu performance? Do the
> additional fields in the productive database have an impact on the
> performance? Or do I miss something?
>
> Regards,
> Christian
>
> --
> Deriva GmbH Tel.: +49 551 489500-42
> Financial IT and Consulting Fax: +49 551 489500-91
> Hans-Böckler-Straße 2 http://www.deriva.de
> D-37079 Göttingen
>
> Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

_________________________________________________________________
Send e-mail faster without improving your typing skills.
http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_speed_122008