Skip site navigation (1) Skip section navigation (2)

Re: How can I make PostgreSQL to select another quey plan?

From: Yangyang <yangyangbm(at)gmail(dot)com>
To: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: How can I make PostgreSQL to select another quey plan?
Date: 2011-11-30 16:07:14
Message-ID: CAN3HncxZ1kVvJjNV8FBE69+4tqYfEnvmkKzopfu3PySM5BiCCg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Thank you so much for the reply, Sean.
1. I created tables and populated data with DBT-3.
2. I'm using TPC-H Q4. I may also test more TPC-H queries.
3. The plan is like:

Sort  (cost=1219522013.16..1219522013.17 rows=1 width=16)
   Sort Key: orders.o_orderpriority
   ->  HashAggregate  (cost=1219521913.15..1219522013.15 rows=1 width=16)
         ->  Seq Scan on orders  (cost=0.00..1219521627.24 rows=57181 width=16)
               Filter: ((o_orderdate >= '1993-07-01'::date) AND
(o_orderdate < '1993-10-01 00:00:00'::timestamp without time zone) AND
(subplan))
               SubPlan
                 ->  Index Scan using lineitem_pkey on lineitem
(cost=0.00..28502.87 rows=93 width=112)
                       Index Cond: (l_orderkey = $0)
                       Filter: (l_commitdate < l_receiptdate)
(9 rows)

4. My question is, is it possible for me to modify some parameters,
therefore the query plan is changed? I have tried some in the
postgres.conf file, but no matter which one I change, the query plan
is the same.
5. My assumption is, if one type of resource is treated more valuable,
it would affect how the DB evaluate costs and thus may choose a
different query plan.

Thank you so much for your time and help.
-Yangyang
On Wed, Nov 30, 2011 at 10:57 AM, Sean Davis <sdavis2(at)mail(dot)nih(dot)gov> wrote:
>
>
> On Wed, Nov 30, 2011 at 10:51 AM, Yangyang <yangyangbm(at)gmail(dot)com> wrote:
>>
>> HI, dear all
>> I'm trying to force the database to select another query plan for a
>> query, rather than the optimal one.
>> I changed query tuning related parameters, but the query plan stays
>> the same. Although the costs for nodes changed correspondingly,
>> What parameters can I modify to force the DB to select another plan?
>> Any hints will be helpful.
>
>
> What is the database structure, query, and plan?  What do you want to have
> changed?
>
> Sean

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2011-11-30 16:11:26
Subject: Re: How can I make PostgreSQL to select another quey plan?
Previous:From: Sean DavisDate: 2011-11-30 15:57:43
Subject: Re: How can I make PostgreSQL to select another quey plan?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group