Hello,
If a table takes 100 MB while on disk, approximately how much space will it
take in RAM/database buffer?
Context - We are designing a database that will hold a couple of GBs of data.
We wanted to figure out how much shared_buffers we should provide to ensure
that most of the time, all th
Hi,
>
>
> Yeah. well, hm. Is the function returning a whole bunch of data?
> Also, try confirming the slow runtime from the server's point of view;
> log_min_duration_statement is a good setting for that.
>
I did try those options. In the end, removing an order by (it was not
necessary) from
Hello all,
I have a plpgsql function that takes a few seconds (less than 5) when
executed from psql. The same function, when invoked from java via a
prepared statement takes a few minutes. There are a few queries in the
function. Out of these, the first query takes input parameters for
filter
Hello,
>
> If that does not help, you'll have to change the query probably. The
> problem is the explain analyze you've provided
> (http://explain.depesz.com/s/MY1) does not match the query from your
> yesterday's post so we can't really help with it.
Thanks for the pointers. I think I posted the
>
> A really interesting part is the sort near the bottom -
>
> -> Sort (cost=1895.95..1896.49 rows=215 width=61) (actual
> time=25.926..711784.723 rows=2673340321 loops=1)
> Sort Key: memmst.memshpsta
> Sort Method: quicksort Memory: 206kB
> -> Nested Loop (cost=0.01..1887.62 r
Hello,
> >
> > Please run EXPLAIN ANALYZE on the query and post that, it's hard to
say
> > what's wrong from just the query plan, without knowing where the time
is
> > actually spent.
> Here is the explain analyze
> http://explain.depesz.com/s/MY1
Going through the url tells me that statis
Hello,
>
> Please run EXPLAIN ANALYZE on the query and post that, it's hard to say
> what's wrong from just the query plan, without knowing where the time is
> actually spent.
Here is the explain analyze
http://explain.depesz.com/s/MY1
Regards,
Jayadevan
DISCLAIMER:
"The information in
Hello,
> Please run EXPLAIN ANALYZE on the query and post that, it's hard to say
> what's wrong from just the query plan, without knowing where the time is
> actually spent. And the schema of the tables involved, and any indexes
> on them. (see also http://wiki.postgresql.org/wiki/SlowQueryQue
Hello all,
I have a query which takes about 20 minutes to execute and retrieves
2000-odd records. The explain for the query is pasted here
http://explain.depesz.com/s/52f
The same query, with similar data structures/indexes and data comes back
in 50 seconds in Oracle. We just ported the product t
Hello,
>The most important spec has been omitted. What's the storage subsystem?
We have storage on SAN, RAID 5.
> > We are suing weblogic.
> ^
> Best. Typo. Ever.
>
> I hear most people who use it want to, you're just brave enough to do it
:-P
I wish I could make a few millions
Hello all,
We are planning to test one of our products, which works with Oracle, on
PostgreSQL. The database size is about 100 GB. It is a product with a
not-so-high load ( about 10 tps - mostly read). My doubts are about
PostgreSQL settings. For Oracle, we give about 4 GB SGA (shared buffer)
Hello,
>
> The distribution of the data is that all but 120,000 rows have null
> values in the _revision_expired column.
>
A shot in the dark - will a partial index on the above column help?
http://www.postgresql.org/docs/current/interactive/indexes-partial.html
http://en.wikipedia.org/wiki/Pa
Hello,
> Is there a way force the db to re-evaluate its execution plan for a FK
> without bouncing the DB?
>
> PostgreSQL 8.1.17
>
> In our latest release our developers have implemented some new foreign
> keys but forgot to create indexes on these keys.
>
> The problem surfaced at one of ou
Hi,
> > If you mostly need the last few prices, I'd definitaly go with the
> > first aproach, its much cleaner. Besides, you can store a date/time
> > per price, so you know when it changed.
We too were using such an approach for 'soft deletes'. Soon we realized
that using a one char valid flag t
Hi
> SQL Server 2008 R2 Query
> select t1.id from table1 t1 inner join table2 t2 on t1.id = t2.id
> and t1.question = t2.question and isnull(t1.response,'ISNULL') <>
> isnull(t2.response,'ISNULL')
>
> Postgres 9.1 Query
> select t1.id from table1 t1 inner join table2 t2 on t1.id = t2.id
> and t
Hello,
> the join column, lsid, is a poor choice for a join column as it is a
> long varchar value (avg length 101 characters) that us only gets
> unique way out on the right hand side.
Would a join on subtring on the 'way out on the right hand side' (did you
mean 'rightmost characters' or 'only
> Yes, It starts out form "where it needs to". Assuming you
>did a pg_start_backup() before you did your base backup?
Thanks. I did.
It uses files like 000B00D9.0020.backupto get the
necessary information?
Regards,
Jayadevan
DISCLAIMER:
"The information in this e-mail
Hello all,
One doubt about how PostgreSQL PITR works. Let us say I have all the
archived WALs for the past week with
archive_command = 'cp -i %p /home/postgres/archive/%f http://www.postgresql.org/mailpref/pgsql-performance
Thank you for the detailed explanation.
Regards,
Jayadevan
DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
cont
Hello,
> PostgreSQL can't currently avoid reading the table, because that's
> where the tuple visibility information is stored. We've been making
> progress toward having some way to avoid reading the table for all
> except very recently written tuples, but we're not there yet (in any
> production
> PostgreSQL can't currently avoid reading the table, because that's
> where the tuple visibility information is stored. We've been making
> progress toward having some way to avoid reading the table for all
> except very recently written tuples, but we're not there yet (in any
> production versio
Hello all,
One query about PostgreSQL's index usage. If I select just one column on
which there is an index (or select only columns on which there is an
index), and the index is used by PostgreSQL, does PostgreSQL avoid table
access if possible? I am trying to understand the differences betwee
I increased shared_buffers and maintenance_work_memto
128MB and 64MB and the restore was over in about 20 minutes. Anyway, I am
learning about PostgreSQL and it is not a critical situation. Thanks for
all the replies.
Regards,
Jayadevan
From: Robert Haas
To: Jayadevan M
Cc: pgsql
Hello all,
I was testing how much time a pg_dump backup would take to get restored.
Initially, I tried it with psql (on a backup taken with pg_dumpall). It
took me about one hour. I felt that I should target for a recovery time of
15 minutes to half an hour. So I went through the blogs/documenta
Hello all,
Apologies for the long mail.
I work for a company that is provides solutions mostly on a Java/Oracle
platform. Recently we moved on of our products to PostgreSQL. The main
reason was PostgreSQL's GIS capabilities and the inability of government
departments (especially road/traffic) to
25 matches
Mail list logo
