Mark Forums Read

Multiple SELECTs cached by Postgres?

Go Back

comp.databases.postgresql comp.databases.postgresql

Reply

Page 1 of 4

1

#1

Old

Johannes Bauer

Posts: n/a

Multiple SELECTs cached by Postgres? - 08-29-2008 , 10:05 AM

Hello group,

I've a performance question about Postgres. Say I have two queries which
are complex and require lots of time (many joins, etc). For my example,
I'll just use the simple:

SELECT a FROM b [many joins etcetc];

Now when I want to know how many rows were returned, I'll issue:

SELECT COUNT(*) FROM b [many joins etcetc];

The question now is: Are those two SELECTs performed twice or is
Postgres smart enough to just return the cached number of rows returned?
If it isn't, is there a way I can make it understand what I want so the
query is only executed once? How about when I embedd the two queries in
one transaction, there obviously has to be some kind of caching, as for
example:

BEGIN TRANSACTION;
SELECT now();
SELECT now();
COMMIT;

Returns twice the same value for "now()", no matter how much time has
passed.

Thanks in advance for any enlightenment,
Kind Regards,
Johannes

Reply With Quote

#2

Old

Nico Latzer

Posts: n/a

Re: Multiple SELECTs cached by Postgres? - 08-31-2008 , 01:39 AM

Hi,

now() is docmented as the transaction start time, so theres no caching
and inside the same transaction its result should always be the same value.

For resolving performance questions EXPLAIN gives You valuable insight.

Regards,
Nico

Johannes Bauer schrieb:

Quote:

Hello group,

I've a performance question about Postgres. Say I have two queries which
are complex and require lots of time (many joins, etc). For my example,
I'll just use the simple:

SELECT a FROM b [many joins etcetc];

Now when I want to know how many rows were returned, I'll issue:

SELECT COUNT(*) FROM b [many joins etcetc];

The question now is: Are those two SELECTs performed twice or is
Postgres smart enough to just return the cached number of rows returned?
If it isn't, is there a way I can make it understand what I want so the
query is only executed once? How about when I embedd the two queries in
one transaction, there obviously has to be some kind of caching, as for
example:

BEGIN TRANSACTION;
SELECT now();
SELECT now();
COMMIT;

Returns twice the same value for "now()", no matter how much time has
passed.

Thanks in advance for any enlightenment,
Kind Regards,
Johannes

Reply With Quote

#3

Old

Nico Latzer

Posts: n/a

Re: Multiple SELECTs cached by Postgres? - 08-31-2008 , 01:39 AM

Hi,

now() is docmented as the transaction start time, so theres no caching
and inside the same transaction its result should always be the same value.

For resolving performance questions EXPLAIN gives You valuable insight.

Regards,
Nico

Johannes Bauer schrieb:

Quote:

Hello group,

I've a performance question about Postgres. Say I have two queries which
are complex and require lots of time (many joins, etc). For my example,
I'll just use the simple:

SELECT a FROM b [many joins etcetc];

Now when I want to know how many rows were returned, I'll issue:

SELECT COUNT(*) FROM b [many joins etcetc];

The question now is: Are those two SELECTs performed twice or is
Postgres smart enough to just return the cached number of rows returned?
If it isn't, is there a way I can make it understand what I want so the
query is only executed once? How about when I embedd the two queries in
one transaction, there obviously has to be some kind of caching, as for
example:

BEGIN TRANSACTION;
SELECT now();
SELECT now();
COMMIT;

Returns twice the same value for "now()", no matter how much time has
passed.

Thanks in advance for any enlightenment,
Kind Regards,
Johannes

Reply With Quote

#4

Old

Nico Latzer

Posts: n/a

Re: Multiple SELECTs cached by Postgres? - 08-31-2008 , 01:39 AM

Hi,

now() is docmented as the transaction start time, so theres no caching
and inside the same transaction its result should always be the same value.

For resolving performance questions EXPLAIN gives You valuable insight.

Regards,
Nico

Johannes Bauer schrieb:

Quote:

Hello group,

I've a performance question about Postgres. Say I have two queries which
are complex and require lots of time (many joins, etc). For my example,
I'll just use the simple:

SELECT a FROM b [many joins etcetc];

Now when I want to know how many rows were returned, I'll issue:

SELECT COUNT(*) FROM b [many joins etcetc];

The question now is: Are those two SELECTs performed twice or is
Postgres smart enough to just return the cached number of rows returned?
If it isn't, is there a way I can make it understand what I want so the
query is only executed once? How about when I embedd the two queries in
one transaction, there obviously has to be some kind of caching, as for
example:

BEGIN TRANSACTION;
SELECT now();
SELECT now();
COMMIT;

Returns twice the same value for "now()", no matter how much time has
passed.

Thanks in advance for any enlightenment,
Kind Regards,
Johannes

Reply With Quote

#5

Old

Nico Latzer

Posts: n/a

Re: Multiple SELECTs cached by Postgres? - 08-31-2008 , 01:39 AM

Hi,

now() is docmented as the transaction start time, so theres no caching
and inside the same transaction its result should always be the same value.

For resolving performance questions EXPLAIN gives You valuable insight.

Regards,
Nico

Johannes Bauer schrieb:

Quote:

Hello group,

I've a performance question about Postgres. Say I have two queries which
are complex and require lots of time (many joins, etc). For my example,
I'll just use the simple:

SELECT a FROM b [many joins etcetc];

Now when I want to know how many rows were returned, I'll issue:

SELECT COUNT(*) FROM b [many joins etcetc];

The question now is: Are those two SELECTs performed twice or is
Postgres smart enough to just return the cached number of rows returned?
If it isn't, is there a way I can make it understand what I want so the
query is only executed once? How about when I embedd the two queries in
one transaction, there obviously has to be some kind of caching, as for
example:

BEGIN TRANSACTION;
SELECT now();
SELECT now();
COMMIT;

Returns twice the same value for "now()", no matter how much time has
passed.

Thanks in advance for any enlightenment,
Kind Regards,
Johannes

Reply With Quote

#6

Old

Nico Latzer

Posts: n/a

Re: Multiple SELECTs cached by Postgres? - 08-31-2008 , 01:39 AM

Hi,

now() is docmented as the transaction start time, so theres no caching
and inside the same transaction its result should always be the same value.

For resolving performance questions EXPLAIN gives You valuable insight.

Regards,
Nico

Johannes Bauer schrieb:

Quote:

Hello group,

I've a performance question about Postgres. Say I have two queries which
are complex and require lots of time (many joins, etc). For my example,
I'll just use the simple:

SELECT a FROM b [many joins etcetc];

Now when I want to know how many rows were returned, I'll issue:

SELECT COUNT(*) FROM b [many joins etcetc];

The question now is: Are those two SELECTs performed twice or is
Postgres smart enough to just return the cached number of rows returned?
If it isn't, is there a way I can make it understand what I want so the
query is only executed once? How about when I embedd the two queries in
one transaction, there obviously has to be some kind of caching, as for
example:

BEGIN TRANSACTION;
SELECT now();
SELECT now();
COMMIT;

Returns twice the same value for "now()", no matter how much time has
passed.

Thanks in advance for any enlightenment,
Kind Regards,
Johannes

Reply With Quote

#7

Old

Nico Latzer

Posts: n/a

Re: Multiple SELECTs cached by Postgres? - 08-31-2008 , 01:39 AM

Hi,

now() is docmented as the transaction start time, so theres no caching
and inside the same transaction its result should always be the same value.

For resolving performance questions EXPLAIN gives You valuable insight.

Regards,
Nico

Johannes Bauer schrieb:

Quote:

Hello group,

I've a performance question about Postgres. Say I have two queries which
are complex and require lots of time (many joins, etc). For my example,
I'll just use the simple:

SELECT a FROM b [many joins etcetc];

Now when I want to know how many rows were returned, I'll issue:

SELECT COUNT(*) FROM b [many joins etcetc];

The question now is: Are those two SELECTs performed twice or is
Postgres smart enough to just return the cached number of rows returned?
If it isn't, is there a way I can make it understand what I want so the
query is only executed once? How about when I embedd the two queries in
one transaction, there obviously has to be some kind of caching, as for
example:

BEGIN TRANSACTION;
SELECT now();
SELECT now();
COMMIT;

Returns twice the same value for "now()", no matter how much time has
passed.

Thanks in advance for any enlightenment,
Kind Regards,
Johannes

Reply With Quote

#8

Old

Nico Latzer

Posts: n/a

Re: Multiple SELECTs cached by Postgres? - 08-31-2008 , 01:39 AM

Hi,

now() is docmented as the transaction start time, so theres no caching
and inside the same transaction its result should always be the same value.

For resolving performance questions EXPLAIN gives You valuable insight.

Regards,
Nico

Johannes Bauer schrieb:

Quote:

Hello group,

I've a performance question about Postgres. Say I have two queries which
are complex and require lots of time (many joins, etc). For my example,
I'll just use the simple:

SELECT a FROM b [many joins etcetc];

Now when I want to know how many rows were returned, I'll issue:

SELECT COUNT(*) FROM b [many joins etcetc];

The question now is: Are those two SELECTs performed twice or is
Postgres smart enough to just return the cached number of rows returned?
If it isn't, is there a way I can make it understand what I want so the
query is only executed once? How about when I embedd the two queries in
one transaction, there obviously has to be some kind of caching, as for
example:

BEGIN TRANSACTION;
SELECT now();
SELECT now();
COMMIT;

Returns twice the same value for "now()", no matter how much time has
passed.

Thanks in advance for any enlightenment,
Kind Regards,
Johannes

Reply With Quote

#9

Old

Nico Latzer

Posts: n/a

Re: Multiple SELECTs cached by Postgres? - 08-31-2008 , 01:39 AM

Hi,

now() is docmented as the transaction start time, so theres no caching
and inside the same transaction its result should always be the same value.

For resolving performance questions EXPLAIN gives You valuable insight.

Regards,
Nico

Johannes Bauer schrieb:

Quote:

Hello group,

I've a performance question about Postgres. Say I have two queries which
are complex and require lots of time (many joins, etc). For my example,
I'll just use the simple:

SELECT a FROM b [many joins etcetc];

Now when I want to know how many rows were returned, I'll issue:

SELECT COUNT(*) FROM b [many joins etcetc];

The question now is: Are those two SELECTs performed twice or is
Postgres smart enough to just return the cached number of rows returned?
If it isn't, is there a way I can make it understand what I want so the
query is only executed once? How about when I embedd the two queries in
one transaction, there obviously has to be some kind of caching, as for
example:

BEGIN TRANSACTION;
SELECT now();
SELECT now();
COMMIT;

Returns twice the same value for "now()", no matter how much time has
passed.

Thanks in advance for any enlightenment,
Kind Regards,
Johannes

Reply With Quote

#10

Old

Johannes Bauer

Posts: n/a

Re: Multiple SELECTs cached by Postgres? - 08-31-2008 , 07:08 AM

Nico Latzer schrieb:

Quote:

now() is docmented as the transaction start time, so theres no caching
and inside the same transaction its result should always be the same value.

For resolving performance questions EXPLAIN gives You valuable insight.

Oh yes, I forget to mention: I tried EXPLAIN, but it returns the same
search tree twice. However I do not know if EXPLAIN knows about caching
and would display that. Therefore I think that maybe the queries might
be cached although EXPLAIN states there is a (for example) seq search.

Regards,
Johannes

Reply With Quote

Reply

Page 1 of 4

1

« Previous Thread | Next Thread »

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.