Re: [PERFORM] execute cursor fetch

[my ho]

Hi, 
If anyone can help pls, I have a question abt the
execution of cursor create/fetch/move , in particular
about disk cost. When a cursor is created, is the
whole table (with the required columns) got put into
memory? otherwise how does it work? (in term of disk
read and transfer?) after user issues command
move/fetch, how does postgre speed up the query in
compare to normal selection?
Thanks a lot, 
regards,
MT Ho 




__
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail 

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] Normal case or bad query plan?

[Aaron Werman]

Makes sense. See DB2 8.2 info on their new implementation of cross column
statistics. If this is common and you're willing to change code, you can
fake that by adding a operation index on some hash function of both columns,
and search for both columns and the hash.

- Original Message - 
From: "Kris Jurka" <[EMAIL PROTECTED]>
To: "Gabriele Bartolini" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, October 11, 2004 5:17 PM
Subject: Re: [PERFORM] Normal case or bad query plan?


>
>
> On Mon, 11 Oct 2004, Gabriele Bartolini wrote:
>
>
> --
---
> >   Seq Scan on ip2location  (cost=0.00..30490.65 rows=124781 width=8)
> > (actual time=5338.120..40237.283 rows=1 loops=1)
> > Filter: ((1040878301::bigint >= ip_address_from) AND
> > (1040878301::bigint <= ip_address_to))
> >   Total runtime: 40237.424 ms
> >
>
> I believe the problem is that pg's lack of cross-column statistics is
> producing the poor number of rows estimate.  The number of rows mataching
> just the first 1040878301::bigint >= ip_address_from condition is 122774
> which is roughtly 10% of the table.  I imagine the query planner
> believes that the other condition alone will match the other 90% of the
> table.  The problem is that it doesn't know that these two ranges'
> intersection is actually tiny.  The planner assumes a complete or nearly
> complete overlap so it thinks it will need to fetch 10% of the rows from
> both the index and the heap and chooses a seqscan.
>
> Kris Jurka
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html
>

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [PERFORM] execute cursor fetch

[Pierre-Frédéric Caillaud]

I just discovered this :
http://www.postgresql.org/docs/7.4/static/jdbc-query.html#AEN24298
On Tue, 12 Oct 2004 04:43:43 -0700 (PDT), my ho <[EMAIL PROTECTED]>  
wrote:

Hi,
If anyone can help pls, I have a question abt the
execution of cursor create/fetch/move , in particular
about disk cost. When a cursor is created, is the
whole table (with the required columns) got put into
memory? otherwise how does it work? (in term of disk
read and transfer?) after user issues command
move/fetch, how does postgre speed up the query in
compare to normal selection?
Thanks a lot,
regards,
MT Ho


__
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [PERFORM] execute cursor fetch

[Stef]

Pierre-Frédéric Caillaud mentioned :
=> http://www.postgresql.org/docs/7.4/static/jdbc-query.html#AEN24298

My question is :
Is this only true for postgres versions >= 7.4 ?

I see the same section about "Setting fetch size to turn cursors on and off"
is not in the postgres 7.3.7 docs. Does this mean 7.3 the JDBC driver
for postgres < 7.4 doesn't support this ?

Kind Regards
Stefan

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] execute cursor fetch

[Tom Lane]

my ho <[EMAIL PROTECTED]> writes:
> If anyone can help pls, I have a question abt the
> execution of cursor create/fetch/move , in particular
> about disk cost. When a cursor is created, is the
> whole table (with the required columns) got put into
> memory?

No.  The plan is set up and then incrementally executed each time you
say FETCH.

> how does postgre speed up the query in
> compare to normal selection?

The only difference from a SELECT is that the planner will prefer
"fast-start" plans, on the theory that you may not be intending
to retrieve the whole result.  For instance it might prefer an
indexscan to a seqscan + sort, when it otherwise wouldn't.

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [PERFORM] Normal case or bad query plan?

[Gabriele Bartolini]

Hi Kris,

>I believe the problem is that pg's lack of cross-column statistics is
>producing the poor number of rows estimate.  The number of rows mataching

I got your point now. I had not understood it last night but it makes really
sense.

>which is roughtly 10% of the table.  I imagine the query planner
>believes that the other condition alone will match the other 90% of the

>table.  The problem is that it doesn't know that these two ranges'
>intersection is actually tiny.  The planner assumes a complete or nearly
>complete overlap so it thinks it will need to fetch 10% of the rows from

Yep, because it performs those checks separately and it gets 10% for one
check and 90% for the other.

As Tom says, I should somehow make PostgreSQL see my data as a single entity
in order to perform a real range check. I will study some way to obtain
it.

However, I got better results by specifying the grane of the statistics
through "ALTER TABLE ... SET STATISTICS".

FYI I set it to 1000 (the maximum) and I reduced the query's estimated time
by the 90% (from 4ms to 4000ms) although much slower than the index
scan (200ms).

I will play a bit with data types as Tom suggested.

For now, thanks anyone who tried and helped me.

Ciao,
-Gabriele


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [PERFORM] Normal case or bad query plan?

[Steinar H. Gunderson]

On Tue, Oct 12, 2004 at 04:29:36PM +0200, Gabriele Bartolini wrote:
> FYI I set it to 1000 (the maximum) and I reduced the query's estimated time
> by the 90% (from 4ms to 4000ms) although much slower than the index
> scan (200ms).

Note that the estimated times are _not_ in ms. They are in multiples of a
disk fetch (?). Thus, you can't compare estimated and real times like that.

/* Steinar */
-- 
Homepage: http://www.sesse.net/

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[PERFORM] Which plattform do you recommend I run PostgreSQL for best performance?

[nd02tsk]

Hello

I am doing a comparison between MySQL and PostgreSQL.

In the MySQL manual it says that MySQL performs best with Linux 2.4 with
ReiserFS on x86. Can anyone official, or in the know, give similar
information regarding PostgreSQL?

Also, any links to benchmarking tests available on the internet between
MySQL and PostgreSQL would be appreciated.

Thank you!

Tim



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] Which plattform do you recommend I run PostgreSQL for

[Matt Clark]

In the MySQL manual it says that MySQL performs best with Linux 2.4 with
ReiserFS on x86. Can anyone official, or in the know, give similar
information regarding PostgreSQL?
 

I'm neither official, nor in the know, but I do have a spare moment! I 
can tell you that any *NIX variant on any modern hardware platform will 
give you good performance, except for Cygwin/x86.  Any differences 
between OSes on the same hardware are completely swamped by far more 
direct concerns like IO systems, database design, OS tuning etc.  Pick 
the OS you're most familiar with is usually a good recommendation (and 
not just for Postgres).

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [PERFORM] Which plattform do you recommend I run PostgreSQL for

[Francisco Reyes]

On Tue, 12 Oct 2004 [EMAIL PROTECTED] wrote:
In the MySQL manual it says that MySQL performs best with Linux 2.4 with
ReiserFS on x86. Can anyone official, or in the know, give similar
information regarding PostgreSQL?
Don't know which OS/filesystem PostgreSQL runs best on, but you should 
test on whatever OS and filesystem you are most experienced.

Whatever speed gain you may get from "best setups" will mean little if 
the machine crashes and you don't know how to fix it and get it back 
up quickly.

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [PERFORM] Which plattform do you recommend I run PostgreSQL for best performance?

[Josh Berkus]

Tim,

> In the MySQL manual it says that MySQL performs best with Linux 2.4 with
> ReiserFS on x86. Can anyone official, or in the know, give similar
> information regarding PostgreSQL?

PostgreSQL runs on a lot more platforms than MySQL; it's not even reasonable 
to compare some of them, like rtLinux, AIX or Cygwin.   The only reasonable 
comparative testing that's been done seems to indicate that:
Linux 2.6 is more efficient than FreeBSD which is more efficient than Linux 
2.4 all of which are significantly more efficient than Solaris, and
ReiserFS, XFS and JFS *seem* to outperform other Linux journalling FSes.

However, as others have said, configuration and hardware will probably make 
more difference than your choice of OS except in extreme cases.   And all of 
the above is being further tested, particularly the filesystems.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [PERFORM] Which plattform do you recommend I run PostgreSQL for best performance?

[Christopher Browne]

In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] transmitted:
> I am doing a comparison between MySQL and PostgreSQL.
>
> In the MySQL manual it says that MySQL performs best with Linux 2.4 with
> ReiserFS on x86. Can anyone official, or in the know, give similar
> information regarding PostgreSQL?

The fastest I have ever seen PostgreSQL run is on an IBM pSeries 650
system using AIX 5.1 and JFS2.  There aren't many Linux systems that
are anywhere _near_ as fast as that.

There's some indication that FreeBSD 4.9, running the Berkeley FFS
filesystem might be the quickest way of utilizing pedestrian IA-32
hardware, although it is _much_ more important to have a system for
which you have a competent sysadmin than it is to have some
"tweaked-out" OS configuration.

In practice, competent people generally prefer to have systems that
hum along nicely as opposed to systems that have ben "tweaked out"
such that any little change will cause them to cave in.  

Benchmarks are useful in determining:

  a) Whether or not it is realistic to attempt a project, and

  b) Whether or not you have made conspicuous errors in configuring
 your systems.

They are notoriously BAD as predictive tools, as the benchmarks
sponsored by vendors get tweaked to make the vendors' products look
good, as opposed to being written to be useful for prediction.

See if you see anything useful from MySQL in this regard:


> Also, any links to benchmarking tests available on the internet
> between MySQL and PostgreSQL would be appreciated.

Most database vendors have licenses that specifically forbid
publishing benchmarks.
-- 
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://www3.sympatico.ca/cbbrowne/oses.html
Do you know where your towel is?

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] Caching of Queries

[Bruce Momjian]

Added to TODO:

* Add RESET CONNECTION command to reset all session state

  This would include resetting of all variables (RESET ALL), dropping of
  all temporary tables, removal of any NOTIFYs, etc.  This could be used
  for connection pooling.  We could also change RESET ALL to have this
  functionality.


---

Tatsuo Ishii wrote:
> > Tatsuo Ishii <[EMAIL PROTECTED]> writes:
> > > First, it's not a particular problem with pgpool. As far as I know any
> > > connection pool solution has exactly the same problem. Second, it's
> > > easy to fix if PostgreSQL provides a functionarity such as:"drop all
> > > temporary tables if any".
> > 
> > I don't like that definition exactly --- it would mean that every time
> > we add more backend-local state, we expect client drivers to know to
> > issue the right incantation to reset that kind of state.
> > 
> > I'm thinking we need to invent a command like "RESET CONNECTION" that
> > resets GUC variables, drops temp tables, forgets active NOTIFYs, and
> > generally does whatever else needs to be done to make the session state
> > appear virgin.  When we add more such state, we can fix it inside the
> > backend without bothering clients.
> 
> Great. It's much better than I propose.
> 
> > I now realize that our "RESET ALL" command for GUC variables was not
> > fully thought out.  We could possibly redefine it as doing the above,
> > but that might break some applications ...
> > 
> > regards, tom lane
> > 
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly