Hello,
The part table contains 60000000 rows, so I think that the 96000 rows
estimated matches in part could match reality.
Currently, the lineitem table contains only one index :
TPCH=# \d lineitem
Table "public.lineitem"
Column | Type | Modifiers
-----------------+-----------------------+-----------
l_orderkey | bigint | not null
l_partkey | bigint | not null
l_suppkey | bigint | not null
l_linenumber | bigint | not null
l_quantity | numeric |
l_extendedprice | numeric |
l_discount | numeric |
l_tax | numeric | not null
l_returnflag | character(1) |
l_linestatus | character(1) |
l_shipdate | date |
l_commitdate | date |
l_receiptdate | date |
l_shipinstruct | character(25) |
l_shipmode | character(10) |
l_comment | character varying(44) |
Indexes:
"i_l_orderkey" btree (l_orderkey), tablespace "tb_index"
Tablespace: "tb_lit"
I think I will try to optimize PostGreSQL in a second time by creating
appropriate indexes.
I don't think that this index is on relevent column for this query.
Regards,
Alexandra DANTE
Martijn van Oosterhout a écrit :
On Tue, Dec 20, 2005 at 01:35:03PM +0100, DANTE ALEXANDRA wrote:
You will find below the explain plan of one of the queries which has
finished with "out of memory". This query contains aggregate and a
sub-select with 6 joins :
1. Firstly, it could be the Hash node. Does the estimated number of
matches in part (96000 rows) match reality?
2. Secondly, looks like lineitem could use an index on partkey. Maybe it
could then use a more efficient join?
Do you have indexes on the relevent columns?
Have a nice day,
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
