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,
>
>
