Hi all,
I am using Postgresql database for our project and doing some
performance testing. We need to insert millions of record with indexed
columns. We have 5 columns in table. I created index on integer only
then performance is good but when I created index on text column as
well then the
On Sat, Jan 28, 2012 at 19:11, Jayashankar K B
jayashankar...@lnties.com wrote:
But we are stumped by the amount of CPU Postgres is eating up.
You still haven't told us *how* slow it actually is and how fast you
need it to be? What's your database layout like (tables, columns,
indexes, foreign
On Mon, Jan 30, 2012 at 6:27 AM, Saurabh saurabh@gmail.com wrote:
Hi all,
I am using Postgresql database for our project and doing some
performance testing. We need to insert millions of record with indexed
columns. We have 5 columns in table. I created index on integer only
then
Update: The main stored function in question and all of its sub
sub-functions were recoded to new pure sql functions.
I then stub tested the sub functions sql vs. plpgsql.
Here were the results for new sql vs old plpgsql:
Individual sub functions tested 20-30% faster
But the main function
On Mon, Jan 30, 2012 at 1:27 AM, Saurabh saurabh@gmail.com wrote:
Hi all,
I am using Postgresql database for our project and doing some
performance testing. We need to insert millions of record with indexed
columns. We have 5 columns in table. I created index on integer only
then
On Mon, Jan 30, 2012 at 2:46 PM, Saurabh saurabh@gmail.com wrote:
max_connections = 100
shared_buffers = 32MB
wal_buffers = 1024KB
checkpoint_segments = 3
That's a default config isn't it?
You'd do well to try and optimize it for your system. The defaults are
really, reeallly
On 1/30/2012 3:27 AM, Saurabh wrote:
Hi all,
I am using Postgresql database for our project and doing some
performance testing. We need to insert millions of record with indexed
columns. We have 5 columns in table. I created index on integer only
then performance is good but when I created
So, here's the query:
SELECT private, COUNT(block_id) FROM blocks WHERE created 'yesterday' AND
shared IS FALSE GROUP BY private
What confuses me is that though this is a largish table (millions of rows)
with constant writes, the query is over indexed columns of types timestamp
and boolean so I
On Mon, Jan 30, 2012 at 4:13 PM, Alessandro Gagliardi
alessan...@path.com wrote:
So, here's the query:
SELECT private, COUNT(block_id) FROM blocks WHERE created 'yesterday' AND
shared IS FALSE GROUP BY private
What confuses me is that though this is a largish table (millions of rows)
with
On Mon, Jan 30, 2012 at 9:46 AM, Saurabh saurabh@gmail.com wrote:
Thank you for the information.
Schema of table is:
ID bigint
company_name text
data_set text
time timestamp
Date date
Length of
Well that was a *lot* faster:
HashAggregate (cost=156301.82..156301.83 rows=2 width=26) (actual
time=2692.806..2692.807 rows=2 loops=1)
- Bitmap Heap Scan on blocks (cost=14810.54..155828.95 rows=472871
width=26) (actual time=289.828..1593.893 rows=575186 loops=1)
Recheck Cond:
On Mon, Jan 30, 2012 at 5:35 PM, Alessandro Gagliardi
alessan...@path.com wrote:
To answer your (non-)question about Heroku, it's a cloud service, so I don't
host PostgreSQL myself. I'm not sure how much I can mess with things like
GUC since I don't even have access to the postgres database on
Hm. Well, it looks like setting enable_seqscan=false is session specific,
so it seems like I can use it with this query alone; but it sounds like
even if that works, it's a bad practice. (Is that true?)
My effective_cache_size is 153kB
On Mon, Jan 30, 2012 at 12:50 PM, Claudio Freire
On Mon, Jan 30, 2012 at 5:55 PM, Alessandro Gagliardi
alessan...@path.com wrote:
Hm. Well, it looks like setting enable_seqscan=false is session specific, so
it seems like I can use it with this query alone; but it sounds like even if
that works, it's a bad practice. (Is that true?)
Yep
My
On Mon, Jan 30, 2012 at 17:35, Alessandro Gagliardi alessan...@path.comwrote:
Well that was a *lot* faster:
HashAggregate (cost=156301.82..156301.83 rows=2 width=26) (actual
time=2692.806..2692.807 rows=2 loops=1)
- Bitmap Heap Scan on blocks (cost=14810.54..155828.95 rows=472871
On 1/30/12 12:59 PM, Claudio Freire wrote:
On Mon, Jan 30, 2012 at 5:55 PM, Alessandro Gagliardi
alessan...@path.com wrote:
Hm. Well, it looks like setting enable_seqscan=false is session specific, so
it seems like I can use it with this query alone; but it sounds like even if
that works,
On Mon, Jan 30, 2012 at 1:25 PM, Josh Berkus j...@agliodbs.com wrote:
You can do SHOW random_page_cost yourself right now, too.
4
I also tried SHOW seq_page_cost and that's 1.
Looking at
http://www.postgresql.org/docs/current/static/runtime-config-query.html#GUC-RANDOM-PAGE-COSTI
wonder if I
On Mon, Jan 30, 2012 at 2:39 PM, Alessandro Gagliardi
alessan...@path.com wrote:
On Mon, Jan 30, 2012 at 1:25 PM, Josh Berkus j...@agliodbs.com wrote:
You can do SHOW random_page_cost yourself right now, too.
4
I also tried SHOW seq_page_cost and that's 1.
Looking
at
Pretty sure. I just ran the same query twice in a row
with enable_seqscan=true and the actual time was on the order of 42
seconds both times. With enable_seqscan=false, it was on the order 3
seconds. Going back to enable_seqscan=true, it's back to 42 seconds. Unless
you're saying that
I set random_page_cost to 2 (with enable_seqscan on) and get the same
performance I got with enable_seqscan off.
So far so good. Now I just need to figure out how to set it globally. :-/
On Mon, Jan 30, 2012 at 1:45 PM, Scott Marlowe scott.marl...@gmail.comwrote:
On Mon, Jan 30, 2012 at 2:39
On Mon, Jan 30, 2012 at 2:55 PM, Alessandro Gagliardi
alessan...@path.com wrote:
I set random_page_cost to 2 (with enable_seqscan on) and get the same
performance I got with enable_seqscan off.
So far so good. Now I just need to figure out how to set it globally. :-/
alter database set
On Mon, Jan 30, 2012 at 3:19 PM, Scott Marlowe scott.marl...@gmail.com wrote:
On Mon, Jan 30, 2012 at 2:55 PM, Alessandro Gagliardi
alessan...@path.com wrote:
I set random_page_cost to 2 (with enable_seqscan on) and get the same
performance I got with enable_seqscan off.
So far so good. Now I
Got it (with a little bit of klutzing around). :) Thanks!
On Mon, Jan 30, 2012 at 2:24 PM, Scott Marlowe scott.marl...@gmail.comwrote:
On Mon, Jan 30, 2012 at 3:19 PM, Scott Marlowe scott.marl...@gmail.com
wrote:
On Mon, Jan 30, 2012 at 2:55 PM, Alessandro Gagliardi
alessan...@path.com
Pavel, thank you very much for your explanation.
Is it possible to define under what conditions that sql procs will
outperform plpgsql ones, and vice-versa?
-Original Message-
From: Pavel Stehule [mailto:pavel.steh...@gmail.com]
Sent: January 30, 2012 2:57 AM
To: Carlo Stonebanks
Cc:
Looking at
http://www.postgresql.org/docs/current/static/runtime-config-query.html#GUC-RANDOM-PAGE-COSTI
wonder if I should try reducing random_page_cost?
Yes, and I should speak to Heroku about reducing it by default. RPC
represents the ratio between the cost of a sequential lookup of a
2012/1/31 Carlo Stonebanks stonec.regis...@sympatico.ca:
Pavel, thank you very much for your explanation.
Is it possible to define under what conditions that sql procs will
outperform plpgsql ones, and vice-versa?
yes, little bit :)
when inlining is possible, then SQL function will be faster
26 matches
Mail list logo
