From: MirrorX <mirrorx@gmail.com>
To: pgsql-performance@postgresql.org
Subject: manually force planner to use of index A vs index B
Date: Sat, 3 Dec 2011 06:34:29 -0800 (PST)
Message-ID: <1322922869210-5044616.post@n5.nabble.com> (raw)
hello to all,
the situation i am facing is this->
table X-> 200 mil rows
index A (date, columnA)
index B (date,columnB,columnC)
the query planner is working properly and for a specific query that selects
from table X where all 3 columns of index B are set, it uses index B.
but, at some point there are some bulk inserts with a different date. when
this happens and i run the query mentioned above the planner is using index
A and not index B.
i guess this happens b/c the planner due to the last analyze statistics has
no values of the new date and so it thinks that it is faster to use index A
than index B since the rows that it will search are few. but that's not the
case so this query takes much longer to finish than it would take if it used
the index B.
i have thought of some work-arounds to resolve this situation. for example i
could change the definition of index A to (columnA,date) and i could also
run an analyze command after every bulk insert. Another option would be to
reduce autovacuum_analyze_scale_factor to a very low value so that analyze
would be forced to be made much more often.
but, instead of these solutions, is there anything else that could lead to a
'better' query plan for this specific case? thx in advance
--
View this message in context: http://postgresql.1045698.n5.nabble.com/manually-force-planner-to-use-of-index-A-vs-index-B-tp504461...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mirrorx@gmail.com
Subject: Re: manually force planner to use of index A vs index B
In-Reply-To: <1322922869210-5044616.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox