Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #183

Re: Is PostgreSQL good?

Show all headers | View raw

Don Y wrote:
>>> Are hints on a par with the 'register' keyword in C?  I.e.,
>>> something you *hope* gives the compiler an insight into your
>>> algorithm -- but, that the compiler is free to *ignore*?
>>> Are there consequences to using a "hint" (e.g., using 'register'
>>> places constraints on what you can do)?

>> Roughly yes. Except that (at least in Oracle) a hint will only
>> be ignored if it is impossible to use (you cannot force an index
>> that cannot be used for this query).

> So, if the hint is "a bad idea", the DBMS won't ignore it, even
> if it "knows better"?  I.e., the DBA can *worsen* performance
> by hinting incorrectly?

Yes.

>>> Are there ways (without using a "hint") to coerce the database
>>> to perform in the way your hint would *hope* to have it behave?
>>> (e.g., can you rewrite the SQL to cause the behavior you seek?)

>> Sometimes, yes. Often a simplified statement will be easier
>> to optimize. If you know the optimizer well, you can also trick
>> it into not considering certain optimizations.
>> The optimizer uses "statistics" which are data about the number
>> and distribution of data in table columns. A lot of times the
>> optimizer will perform better if you collect more data.
>> You can also set database parameters that influence the optimizer.

> Are those parameters effectively "hints of a different color"?
> I.e., hints regarding the makeup of the *data* themselves
> instead of a particular *query*?

No, they are a coarser version of hints.
They tell the database things like "do not use a nested loop join",
while a hint would say "use a merge join for this specific join
operation".
Or "it's really a good idea to use an index even if you think it
is not optimal" compared to "use this index".

>> The effect of choosing a better plan can improve things *a lot*.

> Oh.  Are these situations likely to creep in often and *not*
> be obvious to the DBA ahead of time?  I.e., why would he
> know to "hint" at them yet the optimizer not be able to
> foresee the same issue(s)?

These situations creep up and are not obvious.
Whether they creep up often or not depends on the quality of the
optimizer and the complexity of your queries and data.

Usually hints and other tuning facilities are used after a problem
has been discovered.

They are hard to discover in advance, because the problems usually
only manifest with realistict amounts of data.

>> If you fetch 10 million rows, only to discard them later because
>> you join with an empty result set, that's *much* worse than not
>> fetching anything at all.

> Again, why would the DBA know this is likely to be the case
> but the optimizer (planner) *doesn't*?

First, the planner is not perfect.

Moreover, it is not always possible to know these things in advance.
In the described simple case, the planner might go wrong because
he does not know that the second result set will be empty before he
actually tries it.
The DBA (or more likely the programmer), who has knowledge about
the data that is not evident from the database layout (like "no male
can ever have an abortion") can sometimes know more than the planner.

> That was what I *thought* (intuitively) the role of VACUUM was.
> So, *I*, knowing what I have done and am likely to do *next*,
> can decide (if auto is off) whether it behooves me to vacuum
> *now* or "wait until after this next -- which will be my
> LAST -- query, at which time the DB will be idle"

Not quite, because routine vacuuming is not done by the end user
who executes statements, but by an administrator. The end user will
not have the necessary permissions to vacuum all tables in the database.
With non-automatic vacuum, the DBA can for example schedule a daily
VACUUM of all tables at 11 p.m. because he or she knows that nothing
much is going on at that time.

>> WAL stands for "write ahead log" and is a transaction log.
>> It is a way to provide durability for committed transactions in case
>> of a system failure.

> OK.  So, it's just another "missing feature" -- like buying a car
> without air conditioning (not necessary if you live in a cold
> climate)

[...]

> Thanks for taking the time (and pointers) to shed some light
> on this.  Eventually, the WAL might be significant for me but
> I doubt hints would ever be as, at a minimum, they would require
> me to know a lot more about how the planner worked and how to
> convince it to "do something else".

WAL is unimportant for you only then if data loss is not a
problem for you, and yesterday's backup is good enough.

Yours,
Laurenz Albe 

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

migrating oracle to postgres Username <user@domain.tld> - 2011-06-20 19:28 +0200
  Re: migrating oracle to postgres Mladen Gogala <no@email.here.invalid> - 2011-06-20 20:08 +0000
    Re: migrating oracle to postgres Username <user@domain.tld> - 2011-06-20 23:45 +0200
      Re: migrating oracle to postgres Mladen Gogala <no@email.here.invalid> - 2011-06-20 22:37 +0000
      Re: migrating oracle to postgres Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-21 02:01 +0000
  Re: migrating oracle to postgres "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-21 09:18 +0200
    Re: migrating oracle to postgres Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-21 13:38 +0000
      Is PostgreSQL good? (was: migrating oracle to postgres) "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-22 12:30 +0200
        Re: Is PostgreSQL good? Harry Tuttle <OTPXDAJCSJVU@spammotel.com> - 2011-06-22 13:15 +0200
        Re: Is PostgreSQL good? (was: migrating oracle to postgres) Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-22 12:02 +0000
          Re: Is PostgreSQL good? "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2011-06-22 21:52 +0200
            Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-22 22:43 +0000
          Re: Is PostgreSQL good? (was: migrating oracle to postgres) "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-24 12:29 +0200
            Re: Is PostgreSQL good? Hans Castorp <REWYRLXHEGHO@spammotel.com> - 2011-06-24 12:57 +0200
              Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-24 14:53 +0200
              Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-24 16:32 +0000
            Re: Is PostgreSQL good? (was: migrating oracle to postgres) Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-24 13:53 +0000
              Re: Is PostgreSQL good? (was: migrating oracle to postgres) "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-27 11:17 +0200
                Re: Is PostgreSQL good? (was: migrating oracle to postgres) Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-28 06:11 +0000
                Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-28 10:34 +0200
                Re: Is PostgreSQL good? Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-28 13:30 +0000
                Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-29 09:50 +0200
                Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-29 21:38 +0000
                Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-30 09:14 +0200
                Re: Is PostgreSQL good? Mladen Gogala <mgogala@no.address.invalid> - 2011-06-30 13:22 +0000
            Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-06-30 07:05 -0700
              Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-30 15:41 +0000
                Re: Is PostgreSQL good? Richard Kettlewell <rjk@greenend.org.uk> - 2011-06-30 16:49 +0100
                Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-30 19:24 +0000
                Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-06-30 13:21 -0700
                Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-30 22:42 +0000
                Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-01 11:57 -0700
                Re: Is PostgreSQL good? Mladen Gogala <gogala.mladen@gmail.com> - 2011-07-01 22:58 +0000
                Re: Is PostgreSQL good? Richard Kettlewell <rjk@greenend.org.uk> - 2011-07-01 14:07 +0100
                Re: Is PostgreSQL good? Mladen Gogala <gogala.mladen@gmail.com> - 2011-07-01 13:29 +0000
                Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-06-30 13:17 -0700
                Re: Is PostgreSQL good? Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-30 23:53 +0000
              Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-01 10:13 +0200
                Re: Is PostgreSQL good? Mladen Gogala <gogala.mladen@gmail.com> - 2011-07-01 13:38 +0000
                Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-01 16:58 +0200
                Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-07-01 15:33 +0000
                Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-01 11:39 -0700
                Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-04 16:39 +0200
                Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-04 12:11 -0700
                Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-05 08:58 +0200
                Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-05 11:29 -0700
                Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-06 09:00 +0200
                Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-06 01:00 -0700
                Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-07 09:05 +0200
                Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-07 00:34 -0700
                Re: Is PostgreSQL good? Matthew Woodcraft <mattheww@chiark.greenend.org.uk> - 2011-07-04 20:12 +0100
                Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-04 12:24 -0700
      Re: migrating oracle to postgres Robert Klemme <shortcutter@googlemail.com> - 2011-06-23 17:48 +0200
        Re: migrating oracle to postgres Mladen Gogala <no@email.here.invalid> - 2011-06-23 16:42 +0000
          Re: migrating oracle to postgres Mladen Gogala <no@email.here.invalid> - 2011-06-23 18:57 +0000
          Re: migrating oracle to postgres Robert Klemme <shortcutter@googlemail.com> - 2011-06-26 17:35 +0200
            Re: migrating oracle to postgres Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-26 19:08 +0000
          Re: migrating oracle to postgres Robert Klemme <shortcutter@googlemail.com> - 2011-06-27 00:32 -0700
            Re: migrating oracle to postgres Mladen Gogala <no@email.here.invalid> - 2011-06-28 16:00 +0000
              Re: migrating oracle to postgres Robert Klemme <shortcutter@googlemail.com> - 2011-06-28 22:28 +0200

csiph-web