Thanks! for all your replies, I tried increasing the statistics on
fklistingsourceid to 1000 it made any difference. Then I created an
index on (fklistingsourceid,entrydate) it helped and it was fast.
This index would fix this problem but in general I would like to know
what if there are que
Hi,
I am using Postgres 8.2.4, we have to regularly run some queries on
some big tables to see if we have any data for a particular request. But
sometimes we might not have any matching rows on a particular request as
in this case, when it cant find any matching rows it pretty much scans
th
Hi,
This below query is taking more than 3 minutes to run, as you can see
from the explain plan it is pretty much using all the indexes still it
is slow, nested loops are taking too long. Is there anyway I can improve
this query performance ?
I am using postgres8.2.4. Here are the number
Tom Lane wrote:
Pallav Kalva <[EMAIL PROTECTED]> writes:
why does it have different plans for different values
Because the values occur different numbers of times (or so it thinks
anyway). If the rowcount estimates are far from reality, perhaps
increasing the statistics
Hi,
Postgres 8.2.4 is not using the right plan for different values.
From the below queries listing.addressvaluation table has 19million
records , the other table listing.valuationchangeperiod is just lookup
table with 3 records.
If you can see the explain plans for the statements
Mark Lewis wrote:
On Fri, 2007-08-31 at 12:25 -0400, Pallav Kalva wrote:
Can you please correct me if I am wrong, I want to understand how this
works.
Based on what you said, it will run autovacuum again when it passes 200M
transactions, as SELECTS are transactions too and are going on
Alvaro Herrera wrote:
Pallav Kalva wrote:
Tom Lane wrote:
Probably represents freezing of old tuples, which is a WAL-logged
operation as of 8.2. Is it likely that the data is 200M transactions
old?
If nothing changed on these tables how can it freeze old tuples ?
Does
Tom Lane wrote:
Pallav Kalva <[EMAIL PROTECTED]> writes:
We turned on autovacuums on 8.2 and we have a database which is read
only , it is basically a USPS database used only for address lookups
(only SELECTS, no updates/deletes/inserts).
This database has about 10gi
Hi,
We turned on autovacuums on 8.2 and we have a database which is read
only , it is basically a USPS database used only for address lookups
(only SELECTS, no updates/deletes/inserts).
This database has about 10gig data and yesterday autovacuum started
on this database and all of a s
Hi Tom,
Thanks! for the reply, see my comments below
Tom Lane wrote:
> Pallav Kalva <[EMAIL PROTECTED]> writes:
>
>> We have recently upgraded our production database from 8.0.12 to
>> 8.2.4, We have seen lot of improvements on 8.2.4 side but we are also
>
Hi,
We have recently upgraded our production database from 8.0.12 to
8.2.4, We have seen lot of improvements on 8.2.4 side but we are also
seeing some queries which are slow.
Particularly this below query is really bad in 8.2.4 , I can get
only the explain on this as explain analyze never
Hi,
I am having problems with some of the Insert statements in the prod
database. Our client application is trying into insert some of the
records and it is not going through , they are just hanging. They are
running in a transaction and some how it is not telling us what is it
waiting on . He
Hi,
I am in the process of cleaning up one of our big table, this table
has 187 million records and we need to delete around 100 million of them.
I am deleting around 4-5 million of them daily in order to catchup
with vacuum and also with the archive logs space. So far I have deleted
a
Hi,
Is there anyway we can optimize this sql ? it is doing full table
scan on listing and address table . Postgres version 8.0.2
Thanks!
Pallav.
explain analyze
select listing0_.listingid as col_0_0_,
getmaxdate(listing0_.lastupdate, max(addressval2_.createdate)) as col_1_0_
from listing.li
Hi,
Do we have to vacuum template0 database regularly ? We got this warning this morning while vacuuming databases. As a part of my daily vacuum job I do vacuum of quartz, helix_fdc and affiliate databases which are the
one's which are heavily updated and used. But today I realized that usps,
Hi ,
I am having problem optimizing this query, Postgres optimizer uses a
plan which invloves seq-scan on a table. And when I choose a option to
disable seq-scan it uses index-scan and obviously the query is much faster.
All tables are daily vacummed and analyzed as per docs.
Why can
q Scan on
service s (cost=0.00..1.07 rows=1 width=4) (actual time=0.022..0.027
rows=1 loops=1)
Filter:
(servicename = 'alert'::text)
-> Index Scan using
idx_serviceinstanceparameter_fkservice
Hi ,
I am having problem optimizing this query, Postgres optimizer uses a
plan which invloves seq-scan on a table. And when I choose a option to
disable seq-scan it uses index-scan and obviously the query is much faster.
All tables are daily vacummed and analyzed as per docs.
Why cant p
Hi,
We are migrating to a new server with more memory and also from
postgres 7.4 to postgres 8.0.1 version.
Here are my settings on the current 7.4 version:
OS : RedHat 9
CPUS: 2 hyperthreaded
Memory: 4gig
shared_buffers: 65536
sort_mem: 16384
vacuum_mem: 32768
wal_buffers: 64
effective_cache_
24msec.
Also, i tried to see the matching id for squarefeet in attribute table
there are 800 some records in attribute table for 8 version and 700
something in 7.4 version.
Tom Lane wrote:
Pallav Kalva <[EMAIL PROTECTED]> writes:
On 8
common | attribute | fknamestringid |
On 7.4 I get
select * from pg_stats
where tablename = 'attribute' and attname = 'fknamestringid';
schemaname | tablename |attname | null_frac | avg_width |
n_distinct | most_common_vals
|
most_common_freqs
_
where (text1_.value='squareFeet' and
attribute0_.fknamestringid=text1_.stringid)
and (numericValue='775.0')
Tom Lane wrote:
Pallav Kalva <[EMAIL PROTECTED]> writes:
still doesnt make use of the index on common.attribute table .
What do you get from just plain
quareFeet'::text)
Total runtime: 319.496 ms
Tom Lane wrote:
Pallav Kalva <[EMAIL PROTECTED]> writes:
begin;
alter table common.string drop constraint pk_string_stringid;
explain analyze ... same query ...
rollback;
what do u mean by rollback exactly ? i can drop the pk const
Tom Lane wrote:
Pallav Kalva <[EMAIL PROTECTED]> writes:
I am running this query on postgres 8 beta version and it is not
using the right index, where as if i run the same query on postgres 7.4
version it uses the right index .
1. Beta which, exactly?
Beta 4
2. Have you AN
Hi Folks ,
I am running this query on postgres 8 beta version and it is not
using the right index, where as if i run the same query on postgres 7.4
version it uses the right index . Here are the explain analyze output
for both the versions.can anyone explain this ?
tks.
tables: att
ne of
the table. when i look at the stats on that table they are really wrong,
not sure how to fix them. i run vacuumdb and analyze daily.
Also hyperthreading may not be helping you..
does it do any harm to the system if it is hyperthreaded ?
Dave
Pallav Kalva wrote:
Hi ,
I am experienc
Hi ,
I am experiencing a very bad performance on my production database
lately , all my queries are slowing down. Our application is a webbased
system with lot of selects and updates. I am running "vacuumdb" daily on
all the databases, are the below postgres configuration parameters are
set
John A Meinel wrote:
Pallav Kalva wrote:
Hi Everybody.
I have a table in my production database which gets updated
regularly and the stats on this table in pg_class are totally
wrong. I used to run vacuumdb on the whole database daily once and
when i posted the same problem of wrong
Hi Everybody.
I have a table in my production database which gets updated
regularly and the stats on this table in pg_class are totally wrong. I
used to run vacuumdb on the whole database daily once and when i posted
the same problem of wrong stats in the pg_class most of them from this
Pierre-Frédéric Caillaud wrote:
Your suffering comes from the "where ba.bankaccountID =
u.bankaccountID" in the subselect. It means postgres has to run the
subselect once for each row in Users. You want the subselect to run
only once, and return one (or more?) bankaccountid's, then fetch
Pierre-Frédéric Caillaud wrote:
Just One, user can i have only one bankaccount.
Ah well, in that case :
This is your query :
select userID, fname, lname, email, phone, dateEntered, dateCanceled,
dateSuspended, billAmount, billDate, dateBilled, datePaid, '?' as
searches
from Users u
where
Pierre-Frédéric Caillaud wrote:
Just wanted to know the selectivity of the accountnumber and
routingNumber columns.
I shoulda written :
How many rows do the following queries return :
One or few at most, or a lot ?
Just One, user can i have only one bankaccount.
select userI
Pierre-Frédéric Caillaud wrote:
How many rows do the following queries return :
select userID
from bankaccount ba
where ba.bankaccountID = u.bankaccountID
and ba.accountnumber = '12345678'
select userID
from bankaccount ba
where ba.ba
Hi Everybody,
I have a performance problem with this query , it takes lot of time
on the production database. is there a way to improve it ? i do vacuumdb
on this database and do anlyze on the users table separately daily
select userID, fname, lname, email, phone, dateEntered, dateCanceled,
Tom Lane wrote:
Pallav Kalva <[EMAIL PROTECTED]> writes:
I have a table in my production database which has 500k rows and
from the pg_class it shows the number of "relpages" of
around 750K for this table, the same table copied to a test database
shows "relpages"
.
Thanks!
Pallav
Frank Wiles wrote:
On Thu, 02 Dec 2004 14:11:46 -0500
Pallav Kalva <[EMAIL PROTECTED]> wrote:
Hi ,
I have a table in my production database which has 500k rows and
from the pg_class it shows the number of "relpages" of
around 750K for this table, the same table
Hi ,
I have a table in my production database which has 500k rows and
from the pg_class it shows the number of "relpages" of
around 750K for this table, the same table copied to a test database
shows "relpages" as 35k. I run vacuumdb on the whole
database (not on the table individually but the
Tom Lane wrote:
Pallav Kalva <[EMAIL PROTECTED]> writes:
I have a problem with the below query, when i do explain on the
below query on my live database it doesnt use any index specified on the
tables and does seq scan on the table which is 400k records. But if i
copy the same
Hi,
I have a problem with the below query, when i do explain on the
below query on my live database it doesnt use any index specified on the
tables and does seq scan on the table which is 400k records. But if i
copy the same table onto a different database on a different machine it
uses al
Hi
We are in the process of building a new machine for our production
database. Below you will see some of the harware specs for the machine.
I need some help with setting these parameters (shared buffers,
effective cache, sort mem) in the pg_conf file. Also can anyone explain
the differen
40 matches
Mail list logo
