Re: out of memory during query execution

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