|
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@xxxxxxxxx > To: pgsql-general@xxxxxxxxxxxxxx > Subject: 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general Send e-mail faster without improving your typing skills. Get your Hotmail® account. |
