lately i did sth similar in one of our servers, to keep track of active, idle
and idle in transaction connections so as to make some optimization in the
connection pooling and i didn't notice any serious io activity there (had
the cron job run every minute). so imho unless the server is seriously i
here is the explain analyze output->
server=# explain analyze select count(*) from temp_by_hour where xid > 100
and xdate > now() - interval '1 week';
QUERY
PLAN
-postgres version -> 8.4.4
-os -> redhat 5.6
-specs ->24 cores, 96GB ram, shared_buffers=32 GB
-postgresql.conf -> i havent made any changes as far as the query tuning
parameters are concerned.
#--
# QUERY TUNING
#-
thx for the answer.
- What is the problem, i.e. what behaviour you expect?
- How much data is the table?
- What portion of it matches the conditions?
- What is the index definition?
i think in my first post i provided most of these details but ->
1) what i expect is to be able to understand why
thank you all for your advice. i will try the table partitioning approach to
reduce the size of the tables and to be able to handle them more efficiently
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/cannot-use-multicolumn-index-tp4802634p4806239.html
Sent from the Post
dear all,
i have a table with (approx) 500.000.000 rows. it has several indexes, one
of which is a multicolumn index
for a column that has an id (integer) and a column that has a timestamp. i
have read in the manual that the multicolumn index can be used only if the
clauses of the query are in the
hello Micha,
i think that noone can tell you much without more information about your
system. roughly i would say that you could change the following parameters:
shared_buffers = 1024MB -> 6GB
work_mem = 256MB -> 30-50 MB
effective_cache_size = 5120MB -> 16GB (depends on whether its a dedicated d
can you run an analyze command first and then post here the results of:
select * FROM pg_stats WHERE tablename = 'delayed_jobs';
?
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/index-usage-for-min-vs-order-by-asc-limit-1-tp5002928p5004410.html
Sent from the PostgreSQL
hello to all,
i would like your advice on the following matter. i have a table with 150
million rows. there are some indexes on this table but the one that is
really important is one that has 3 columns (a,b,c). one application
constantly makes queries and the query planner uses this index to narro
dear all,
i am trying to understand if i am missing something on how vacuum works. i
ve read the manual, and did some research on the web about that but i am
still not sure.
to my understanding, vacuum just marks the dead rows of a table so that from
that point on that space would be re-used for
thx a lot for your answer :)
so when a transaction is still open from a while back (according to the
transactionID), no 'new dead' tuples can be marked as re-usable space for
new rows, right? by 'new dead' i mean that for example there is a
transaction running from 10.00am(with a specific transact
from what i ve read and have i ve seen in practice, i expected it to do
nothing at all. i just wanted to be absolutely sure and that's why i asked
here.
thank you very much for the clarification
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/vacuum-internals-and-perform
hello to all,
the situation i am facing is this->
table X-> 200 mil rows
index A (date, columnA)
index B (date,columnB,columnC)
the query planner is working properly and for a specific query that selects
from table X where all 3 columns of index B are set, it uses index B.
but, at some point the
thx a lot for the reply. i will post the query plans when a new bulk insert
will take place :)
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/manually-force-planner-to-use-of-index-A-vs-index-B-tp5044616p5044691.html
Sent from the PostgreSQL - performance mailing list ar
thx a lot for your answer :)
2) default_statistics_target is set to (default) 100 and there no special
statistics per-column
3) i will do that very soon
4) in absolute units i can see the same query having similar stats to these:
parse -> 600 ms
bind -> 300 ms
execute -> 50 ms
the query mention
there are some performance issues on a server and by searching in the logs i
noticed that the phases of parse and bind take considerably more time than
execute for most of the queries. i guess that the right thing to do in this
case is to use functions or prepare statements but in any case, what co
the version of postgres is 8.4.7 :)
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/parse-bind-take-more-time-than-execute-tp5102940p5102954.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql
i am not using prepared statements for now :)
i just said that probably, if i do use them, i will get rid of that extra
time since the plan will be already 'decided' in advance
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/parse-bind-take-more-time-than-execute-tp51029
thx for your reply :)
-the timings come from the log
-the table is this ->
\d configurations
Table "public.configurationcontext"
Column | Type | Modifiers
---++---
id| numeric(18,0) |
i remember having a server with 8.4.4 where we had multiple problems with
autovacuum.
if i am not mistaken there are some bugs related with vacuum until 8.4.7.
i would suggest you to upgrade to the latest 8.4.x version
BR,
--
View this message in context:
http://postgresql.1045698.n5.nabble.c
20 matches
Mail list logo
