pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to uses self query plan
9+ messages / 4 participants
[nested] [flat]
* How to uses self query plan
@ 2007-12-18 22:21 olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
2007-12-18 22:46 ` Re: How to uses self query plan Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: olivier.boissard@cerene.fr @ 2007-12-18 22:21 UTC (permalink / raw)
To: pgsql-admin
Hello,
My purpose is to uses different query plan depending on queries
I got the following problem : some queries are taking too much time and
postgresql does not take the most relevant index in account.
I search on internet and found that no HINT function was available in
postgresl.
I tried to change indexes parameters in postgresql.conf but eache time I
set a param to OFF (example nested_loop ,seqscan, ...) , some queries
freeze database .
How can we force postgresql to use a plan ?
olivier
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to uses self query plan
2007-12-18 22:21 How to uses self query plan olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
@ 2007-12-18 22:46 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-12-19 07:57 ` Re: How to uses self query plan olivier boissard <olivier.boissard@cerene.fr>
0 siblings, 1 reply; 9+ messages in thread
From: Scott Marlowe @ 2007-12-18 22:46 UTC (permalink / raw)
To: olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>; +Cc: pgsql-admin
On Dec 18, 2007 4:21 PM, olivier.boissard@cerene.fr
<olivier.boissard@cerene.fr> wrote:
> Hello,
>
> My purpose is to uses different query plan depending on queries
> I got the following problem : some queries are taking too much time and
> postgresql does not take the most relevant index in account.
> I search on internet and found that no HINT function was available in
> postgresl.
> I tried to change indexes parameters in postgresql.conf but eache time I
> set a param to OFF (example nested_loop ,seqscan, ...) , some queries
> freeze database .
>
> How can we force postgresql to use a plan ?
That's not how we do things in postgresql land (usually).
You should figure out WHY your queries are picking the wrong plan, and
then see if you can get them to pick the right ones. If it's a query
planner bug, you report it here, or the perform or general lists, and
it gets fixed. Generic hints aren't likely to happen any time soon,
although I do believe 8.3 is introducing function costing of some
kind, which seems like a useful idea.
But, back to fixing your slow queries.
1: Increase statistics targets on the guilty columns and reanalyze.
2: Run explain analyze select.... and post the output here.
3: ???
4: profit?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to uses self query plan
2007-12-18 22:21 How to uses self query plan olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
2007-12-18 22:46 ` Re: How to uses self query plan Scott Marlowe <scott.marlowe@gmail.com>
@ 2007-12-19 07:57 ` olivier boissard <olivier.boissard@cerene.fr>
2007-12-19 15:33 ` Re: How to uses self query plan Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: olivier boissard @ 2007-12-19 07:57 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
Scott Marlowe a écrit :
> On Dec 18, 2007 4:21 PM, olivier.boissard@cerene.fr
> <olivier.boissard@cerene.fr> wrote:
>
>> Hello,
>>
>> My purpose is to uses different query plan depending on queries
>> I got the following problem : some queries are taking too much time and
>> postgresql does not take the most relevant index in account.
>> I search on internet and found that no HINT function was available in
>> postgresl.
>> I tried to change indexes parameters in postgresql.conf but eache time I
>> set a param to OFF (example nested_loop ,seqscan, ...) , some queries
>> freeze database .
>>
>> How can we force postgresql to use a plan ?
>>
>
> That's not how we do things in postgresql land (usually).
>
> You should figure out WHY your queries are picking the wrong plan, and
> then see if you can get them to pick the right ones. If it's a query
> planner bug, you report it here, or the perform or general lists, and
> it gets fixed. Generic hints aren't likely to happen any time soon,
> although I do believe 8.3 is introducing function costing of some
> kind, which seems like a useful idea.
>
> But, back to fixing your slow queries.
>
> 1: Increase statistics targets on the guilty columns and reanalyze.
> 2: Run explain analyze select.... and post the output here.
> 3: ???
> 4: profit?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
>
Thanks for reply
I will extract some queries.
I want to precise that I use postgresql 8.1.0 .
Olivier
Attachments:
olivier_boissard.vcf (text/x-vcard, 1.1K) download
begin:vcard
fn:Olivier Boissard
n:Boissard;Olivier
org;quoted-printable:CERENE SERVICES;d=C3=A9veloppement
adr:;;3 rue archimede;La Chapelle Saint Luc;AUBE;10600;FRANCE
email;internet:olivier.boissard@cerene.fr
tel;work:+33.3.25.74.11.78
tel;fax:+33.3.25.78.39.67
note;quoted-printable:"Ce message peut contenir des informations confidentielles et/ou=0D=0A=
prot=C3=A9g=C3=A9es. Il est =C3=A0 l'usage exclusif de son destinataire.=
Toute=0D=0A=
utilisation non autoris=C3=A9e peut =C3=AAtre illicite. Si vous recevez=
ce=0D=0A=
message par erreur, nous vous remercions d'en aviser imm=C3=A9diatement=0D=0A=
l'exp=C3=A9diteur en utilisant la fonction r=C3=A9ponse de votre gestionn=
aire=0D=0A=
de courrier =C3=A9lectronique."=0D=0A=
=0D=0A=
"This email may contain confidential information and/or copyright=0D=0A=
material. This email is intended for the use of the addressee only.=0D=0A=
Any unauthorised use may be unlawful. If you receive this email by=0D=0A=
mistake, please advise the sender immediately by using the reply=0D=0A=
facility in your email software."
url:www.cerene.fr
version:2.1
end:vcard
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to uses self query plan
2007-12-18 22:21 How to uses self query plan olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
2007-12-18 22:46 ` Re: How to uses self query plan Scott Marlowe <scott.marlowe@gmail.com>
2007-12-19 07:57 ` Re: How to uses self query plan olivier boissard <olivier.boissard@cerene.fr>
@ 2007-12-19 15:33 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-12-19 22:23 ` Re: How to uses self query plan olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
0 siblings, 1 reply; 9+ messages in thread
From: Scott Marlowe @ 2007-12-19 15:33 UTC (permalink / raw)
To: olivier boissard <olivier.boissard@cerene.fr>; +Cc: pgsql-admin
On Dec 19, 2007 1:57 AM, olivier boissard <olivier.boissard@cerene.fr> wrote:
> Scott Marlowe a écrit :
>
> > On Dec 18, 2007 4:21 PM, olivier.boissard@cerene.fr
> > <olivier.boissard@cerene.fr> wrote:
> >
> >> Hello,
> >>
> >> My purpose is to uses different query plan depending on queries
> >> I got the following problem : some queries are taking too much time and
> >> postgresql does not take the most relevant index in account.
> >> I search on internet and found that no HINT function was available in
> >> postgresl.
> >> I tried to change indexes parameters in postgresql.conf but eache time I
> >> set a param to OFF (example nested_loop ,seqscan, ...) , some queries
> >> freeze database .
> >>
> >> How can we force postgresql to use a plan ?
> >>
> >
> > That's not how we do things in postgresql land (usually).
> >
> > You should figure out WHY your queries are picking the wrong plan, and
> > then see if you can get them to pick the right ones. If it's a query
> > planner bug, you report it here, or the perform or general lists, and
> > it gets fixed. Generic hints aren't likely to happen any time soon,
> > although I do believe 8.3 is introducing function costing of some
> > kind, which seems like a useful idea.
> >
> > But, back to fixing your slow queries.
> >
> > 1: Increase statistics targets on the guilty columns and reanalyze.
> > 2: Run explain analyze select.... and post the output here.
> > 3: ???
> > 4: profit?
> >
> Thanks for reply
>
> I will extract some queries.
> I want to precise that I use postgresql 8.1.0 .
First step would be to update to the latest 8.1.x version, whatever
that is. There have been a LOT of fixes in the 8.1 branch since 8.1.0
came out, and one might be a fix for your query problem. minor
updates are strictly bug and security fixes, so they're not likely to
cause any problems.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to uses self query plan
2007-12-18 22:21 How to uses self query plan olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
2007-12-18 22:46 ` Re: How to uses self query plan Scott Marlowe <scott.marlowe@gmail.com>
2007-12-19 07:57 ` Re: How to uses self query plan olivier boissard <olivier.boissard@cerene.fr>
2007-12-19 15:33 ` Re: How to uses self query plan Scott Marlowe <scott.marlowe@gmail.com>
@ 2007-12-19 22:23 ` olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
2007-12-19 22:35 ` Re: How to uses self query plan Guillaume Lelarge <guillaume@lelarge.info>
2007-12-19 22:42 ` Re: How to uses self query plan Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: olivier.boissard@cerene.fr @ 2007-12-19 22:23 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
You're right .
I will begin by migrate postgresql version.
postgresql is now to 8.3 version . A lot of performance inprovements
have been made in this last version .
In any case I must do a pg_dump/pg_restore.
So , Should I try directly 8.3 version or is it more cautious to stay in
8.1.x version ?
Olivier
Scott Marlowe a écrit :
> On Dec 19, 2007 1:57 AM, olivier boissard <olivier.boissard@cerene.fr> wrote:
>
>> Scott Marlowe a écrit :
>>
>>
>>> On Dec 18, 2007 4:21 PM, olivier.boissard@cerene.fr
>>> <olivier.boissard@cerene.fr> wrote:
>>>
>>>
>>>> Hello,
>>>>
>>>> My purpose is to uses different query plan depending on queries
>>>> I got the following problem : some queries are taking too much time and
>>>> postgresql does not take the most relevant index in account.
>>>> I search on internet and found that no HINT function was available in
>>>> postgresl.
>>>> I tried to change indexes parameters in postgresql.conf but eache time I
>>>> set a param to OFF (example nested_loop ,seqscan, ...) , some queries
>>>> freeze database .
>>>>
>>>> How can we force postgresql to use a plan ?
>>>>
>>>>
>>> That's not how we do things in postgresql land (usually).
>>>
>>> You should figure out WHY your queries are picking the wrong plan, and
>>> then see if you can get them to pick the right ones. If it's a query
>>> planner bug, you report it here, or the perform or general lists, and
>>> it gets fixed. Generic hints aren't likely to happen any time soon,
>>> although I do believe 8.3 is introducing function costing of some
>>> kind, which seems like a useful idea.
>>>
>>> But, back to fixing your slow queries.
>>>
>>> 1: Increase statistics targets on the guilty columns and reanalyze.
>>> 2: Run explain analyze select.... and post the output here.
>>> 3: ???
>>> 4: profit?
>>>
>>>
>> Thanks for reply
>>
>> I will extract some queries.
>> I want to precise that I use postgresql 8.1.0 .
>>
>
> First step would be to update to the latest 8.1.x version, whatever
> that is. There have been a LOT of fixes in the 8.1 branch since 8.1.0
> came out, and one might be a fix for your query problem. minor
> updates are strictly bug and security fixes, so they're not likely to
> cause any problems.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to uses self query plan
2007-12-18 22:21 How to uses self query plan olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
2007-12-18 22:46 ` Re: How to uses self query plan Scott Marlowe <scott.marlowe@gmail.com>
2007-12-19 07:57 ` Re: How to uses self query plan olivier boissard <olivier.boissard@cerene.fr>
2007-12-19 15:33 ` Re: How to uses self query plan Scott Marlowe <scott.marlowe@gmail.com>
2007-12-19 22:23 ` Re: How to uses self query plan olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
@ 2007-12-19 22:35 ` Guillaume Lelarge <guillaume@lelarge.info>
2007-12-19 23:17 ` Re: How to uses self query plan olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
1 sibling, 1 reply; 9+ messages in thread
From: Guillaume Lelarge @ 2007-12-19 22:35 UTC (permalink / raw)
To: olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
olivier.boissard@cerene.fr wrote:
> You're right .
> I will begin by migrate postgresql version.
> postgresql is now to 8.3 version . A lot of performance inprovements
> have been made in this last version .
> In any case I must do a pg_dump/pg_restore.
> So , Should I try directly 8.3 version or is it more cautious to stay in
> 8.1.x version ?
>
It mostly depends on you. PostgreSQL 8.3 is still in beta phase. So, if
you're also on development stage, you can use 8.3. Otherwise, it's
better to use the latest stable available, which means 8.2.5.
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to uses self query plan
2007-12-18 22:21 How to uses self query plan olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
2007-12-18 22:46 ` Re: How to uses self query plan Scott Marlowe <scott.marlowe@gmail.com>
2007-12-19 07:57 ` Re: How to uses self query plan olivier boissard <olivier.boissard@cerene.fr>
2007-12-19 15:33 ` Re: How to uses self query plan Scott Marlowe <scott.marlowe@gmail.com>
2007-12-19 22:23 ` Re: How to uses self query plan olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
2007-12-19 22:35 ` Re: How to uses self query plan Guillaume Lelarge <guillaume@lelarge.info>
@ 2007-12-19 23:17 ` olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
2007-12-19 23:25 ` Re: How to uses self query plan Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: olivier.boissard@cerene.fr @ 2007-12-19 23:17 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
Guillaume Lelarge a écrit :
> olivier.boissard@cerene.fr wrote:
>
>> You're right .
>> I will begin by migrate postgresql version.
>> postgresql is now to 8.3 version . A lot of performance inprovements
>> have been made in this last version .
>> In any case I must do a pg_dump/pg_restore.
>> So , Should I try directly 8.3 version or is it more cautious to stay in
>> 8.1.x version ?
>>
>>
>
> It mostly depends on you. PostgreSQL 8.3 is still in beta phase. So, if
> you're also on development stage, you can use 8.3. Otherwise, it's
> better to use the latest stable available, which means 8.2.5.
>
>
>
I want in fact to make test on a server before , when validated migrate
on production server , so 8.2.5 is more appropriate.
Will I be able to keep my postgresql.conf file or is it not compatible ?
Olivier
.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to uses self query plan
2007-12-18 22:21 How to uses self query plan olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
2007-12-18 22:46 ` Re: How to uses self query plan Scott Marlowe <scott.marlowe@gmail.com>
2007-12-19 07:57 ` Re: How to uses self query plan olivier boissard <olivier.boissard@cerene.fr>
2007-12-19 15:33 ` Re: How to uses self query plan Scott Marlowe <scott.marlowe@gmail.com>
2007-12-19 22:23 ` Re: How to uses self query plan olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
2007-12-19 22:35 ` Re: How to uses self query plan Guillaume Lelarge <guillaume@lelarge.info>
2007-12-19 23:17 ` Re: How to uses self query plan olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
@ 2007-12-19 23:25 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Scott Marlowe @ 2007-12-19 23:25 UTC (permalink / raw)
To: olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>; +Cc: Guillaume Lelarge <guillaume@lelarge.info>; pgsql-admin
On Dec 19, 2007 5:17 PM, olivier.boissard@cerene.fr
<olivier.boissard@cerene.fr> wrote:
> Guillaume Lelarge a écrit :
> > olivier.boissard@cerene.fr wrote:
> >
> >> You're right .
> >> I will begin by migrate postgresql version.
> >> postgresql is now to 8.3 version . A lot of performance inprovements
> >> have been made in this last version .
> >> In any case I must do a pg_dump/pg_restore.
> >> So , Should I try directly 8.3 version or is it more cautious to stay in
> >> 8.1.x version ?
> >>
> >>
> >
> > It mostly depends on you. PostgreSQL 8.3 is still in beta phase. So, if
> > you're also on development stage, you can use 8.3. Otherwise, it's
> > better to use the latest stable available, which means 8.2.5.
> >
> >
> >
> I want in fact to make test on a server before , when validated migrate
> on production server , so 8.2.5 is more appropriate.
> Will I be able to keep my postgresql.conf file or is it not compatible ?
No, you'll need to edit the new one by hand to reflect the changes
you've made in the old one. Some things get renamed from one to the
next, and often newer versions of pgsql have new settings you that the
old version's postgresql.conf wouldn't know about.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to uses self query plan
2007-12-18 22:21 How to uses self query plan olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
2007-12-18 22:46 ` Re: How to uses self query plan Scott Marlowe <scott.marlowe@gmail.com>
2007-12-19 07:57 ` Re: How to uses self query plan olivier boissard <olivier.boissard@cerene.fr>
2007-12-19 15:33 ` Re: How to uses self query plan Scott Marlowe <scott.marlowe@gmail.com>
2007-12-19 22:23 ` Re: How to uses self query plan olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
@ 2007-12-19 22:42 ` Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Scott Marlowe @ 2007-12-19 22:42 UTC (permalink / raw)
To: olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>; +Cc: pgsql-admin
On Dec 19, 2007 4:23 PM, olivier.boissard@cerene.fr
<olivier.boissard@cerene.fr> wrote:
> You're right .
> I will begin by migrate postgresql version.
> postgresql is now to 8.3 version . A lot of performance inprovements
> have been made in this last version .
> In any case I must do a pg_dump/pg_restore.
> So , Should I try directly 8.3 version or is it more cautious to stay in
> 8.1.x version ?
No, not what I meant! I meant to go to the latest 8.1 version. i.e.
8.1.0 has lots of bugs that 8.1.10 (or whatever the latest 8.1 is) has
fixed. If you wanna migrate to 8.2.5 that would probably be ok, but
8.3 isn't quite done yet.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-12-19 23:25 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-18 22:21 How to uses self query plan olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
2007-12-18 22:46 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-12-19 07:57 ` olivier boissard <olivier.boissard@cerene.fr>
2007-12-19 15:33 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-12-19 22:23 ` olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
2007-12-19 22:35 ` Guillaume Lelarge <guillaume@lelarge.info>
2007-12-19 23:17 ` olivier.boissard@cerene.fr <olivier.boissard@cerene.fr>
2007-12-19 23:25 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-12-19 22:42 ` Scott Marlowe <scott.marlowe@gmail.com>
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