On Fri, Jun 21, 2013 at 9:08 AM, bricklen brick...@gmail.com wrote:
Did you try an index on (type, ts desc) ? I don't have much else to add at
this point, but maybe after posting some more server and table (parent and
child) details someone will have an answer for you.
No, this is exactly
I'm trying to optimize a query on a partitioned table. The schema looks
like this:
CREATE TABLE observations(
ts timestamptz NOT NULL DEFAULT now(),
type text NOT NULL,
subject uuid NOT NULL,
details json NOT NULL
);
The table is partitioned by ts (right now I have ~300 1h partitions,
On Thu, Jun 20, 2013 at 9:13 PM, bricklen brick...@gmail.com wrote:
On Thu, Jun 20, 2013 at 6:24 PM, Maciek Sakrejda m.sakre...@gmail.comwrote:
SELECT
DISTINCT ON (type) ts, type, details
FROM
observations
WHERE
subject = '...'
ORDER BY
type, ts DESC;
First thing: What
On Sat, Feb 23, 2013 at 3:53 PM, Jeff Janes jeff.ja...@gmail.com wrote:
It would really help to have explain (analyze, buffers). Especially if
you turn on track_io_timing, (although that part probably can't be done on
Heroku, as it requires superuser access.)
Right, that's not supported right
On Fri, Feb 22, 2013 at 9:59 AM, Vitalii Tymchyshyn tiv...@gmail.com wrote:
Tuning Postgre is not an option, as the instance
is provided by Heroku and as far as I know cannot be tuned by me.
Most tuning parameters can be set at per-query basis, so you can issue
alter database set param=value
On Tue, Oct 30, 2012 at 2:24 AM, vignesh vignes...@snovabits.net wrote:
Hi,
When i start my postgres. Iam getting this error.
You may want to ask on the pgsql-general mailing list [1]. This list
is just for Postgres performance questions. While, technically,
failing to start outright
We've run into a perplexing issue with a customer database. He moved
from a 9.1.5 to a 9.1.6 and upgraded from an EC2 m1.medium (3.75GB
RAM, 1.3 GB shmmax), to an m2.xlarge (17GB RAM, 5.7 GB shmmax), and is
now regularly getting constant errors regarding running out of shared
memory (there were
On Wed, Oct 17, 2012 at 1:53 AM, Martin French
martin.fre...@romaxtech.com wrote:
Thanks for your response.
What are the settings for:
work_mem
100MB
maintenance_work_mem
64MB
How many concurrent connections are there?
~20
Have you ran explain analyze on the query that doesn't crash
On Wed, Oct 17, 2012 at 7:18 AM, Tom Lane t...@sss.pgh.pa.us wrote:
ERROR: out of shared memory
HINT: You might need to increase max_pred_locks_per_transaction.
This has nothing to do with work_mem nor maintenance_work_mem; rather,
it means you're running out of space in the database-wide
Presumably something like this?:
maciek=# CREATE TABLE test AS SELECT g, random() FROM
generate_series(1,1000) g;
CREATE
maciek=# EXPLAIN DELETE FROM test USING (SELECT g FROM test ORDER BY
ctid) x where x.g = test.g;
QUERY PLAN
unfortunately postgres jdbc is bugged and does not honor the above for
transaction control commands (begin, commit, etc). This patch
http://treehou.se/~omar/postgresql-jdbc-8.4-701-pgbouncer_txn.patch
will fix it, assuming it hasn't been fixed in recent postgres jdbc.
Looks like it's still
On Wed, Jul 25, 2012 at 7:13 PM, Rural Hunter ruralhun...@gmail.com wrote:
Why not just use simple Statement instead of PreparedStatement and construct
the SQL with concated string or StringBuilder? like this:
int col1=xxx;
String col2=;
String sql=select * from table where col1=+col+ and
This may be another issue of the problem discussed here:
http://postgresql.1045698.n5.nabble.com/avoid-prepared-statements-on-complex-queries-td4996363.html
(Kris Jurka explains the crux of it in that thread).
Note that it seems the preparing/planning interaction was not the
poster's actual
On Wed, Jul 11, 2012 at 5:47 PM, Yan Chunlu springri...@gmail.com wrote:
I learnt a lot during the back and forth!
Great to hear.
1, postgresql always have 400+ connections(dozens of python process using
client pool)
Note that Postgres does not deal well with a large number of
If I am correct, JDBC uses named portal only on the 5th time you use
PreparedStatement (configurable). Before it uses unnamed thing that should
work as if you did embed the value.
If this is due to the difference in parameter type information, this
doesn't have anything to do with named
My problem is that the server works very slow.
Someone may chime in with general advice, but for more details, can
you be more specific? E.g.,
http://wiki.postgresql.org/wiki/Slow_Query_Questions
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA
he spent much time on running
virtualized (which certainly could affect things). Then if you have
*specific* hardware or query questions, this list is a great resource.
[1]: http://www.2ndquadrant.com/books/postgresql-9-0-high-performance/
---
Maciek Sakrejda | System Architect | Truviso
1065 E
) != 0 and host IP
That's an excellent idea, but note that this will also log
unsuccessful connection attempts (that is, successful TCP connections
that fail PostgreSQL authentication) without much of a way to
distinguish the two, especially if the connections are encrypted.
---
Maciek Sakrejda
. While I understand that this is not simple, many users
will not look outside of standard docs, especially when first
evaluating PostgreSQL. Merlin is right that the current wording does
not really mention a down side to cranking shared_buffers on a system
with plenty of RAM.
---
Maciek Sakrejda
fashion (well,
theoretically, something like selection sort could, but that's beside
the point) so it needs to do all the work up front. I'm no explain
expert, so someone please correct me if I'm wrong.
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City
Does someone can help me?
You may want to try pgsql-general instead of this list.
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com
--
Sent via pgsql-performance mailing list (pgsql-performance
apply.
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org
I have 8-core server, I wanted to ask whether a query can be divided for
multiple processors or cores, if it could be what to do in postgresql
No, at this time (and for the foreseeable future), a single query will
run on a single core.
---
Maciek Sakrejda | System Architect | Truviso
1065 E
to date before you test this.
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http
But you are using stdin for COPY! The best way is use files.
I've never heard this before, and I don't see how reading from files
could possibly help. Can you clarify?
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
to perform better or (ideally) optimize your application so you don't
need such an expensive query (because the fundamental problem is that
this query is inherently expensive).
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
omit rows from the
join result if you skip the visibility check?
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make
can't reproduce with EXPLAIN ANALYZE (which actually runs
the query), how are you reproducing this?
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com
--
Sent via pgsql-performance mailing list (pgsql
I'll get to it as soon as everyone disconnects..
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your
) seems reasonable (I have no strong
feelings there either way), and (2) is probably a moot point (the
behavior won't change in a backward-incompatible manner now, and if
it's dethroned as default, that doesn't really matter).
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd
The hints are there because they are definitely needed. Yet, there is a
religious zeal and a fatwa against them.
The opposition is philosophical, not religious. There is no fatwa.
If you want a serious discussion, avoid inflammatory terms.
---
Maciek Sakrejda | System Architect | Truviso
1065
I was wandering if I could see somehwere the implementation of
pg_stat_activity view
From psql
\d+ pg_stat_activity
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
www.truviso.com
--
Sent via pgsql-performance mailing list (pgsql
believe as far as the server is concerned, pg_dump is just another
client), but if you're concerned about this, you can add the client
pid (%p) to log_line_prefix in postgresql.conf, log the pg_dump pid
through whatever mechanism manages that, and compare.
---
Maciek Sakrejda | System Architect
one of their developers (maybe through
their mailing lists or forums?) and check.
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com
--
Sent via pgsql-performance mailing list (pgsql-performance
-based COPY IN or OUT. We've been using it for several years and
it works like a charm. For more details, ask the JDBC list or check
out the docs: http://jdbc.postgresql.org/documentation/publicapi/index.html
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster
to hit up every business
in the area to donate whatever they can, you're better off canvasing
the neighborhood.
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com
--
Sent via pgsql-performance mailing list
You could check for volatile functions. I think this could be done safely.
I don't think that's enough. A UDA like last() could have an immutable
sfunc, but still be sensitive to the sort order. I think you'd need
something like a special order-sensitive aggregate definition flag.
---
Maciek
before the LIMIT (so you have
to sort everything before you take the first 15). If it were the other
way around, you would take the first 15 rows Postgres happens to find
(in an arbitrary order) and then sort these 15, which is probably not
that useful. Consider Thom's suggestion.
---
Maciek
cqar2 ON (cqar1.b = cqar2.b AND
cqar2.type != 'o') WHERE cqar1.type = 'o') candidate_run LEFT OUTER
JOIN bar ON (candidate_run.type_o_run = bar.b) WHERE non_type_o_run IS
NULL AND bar.b IS NULL);
Thanks,
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster
the equivalence of
semantics is much easier to verify here, we may go with this (at least
for the moment).
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 230
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com
--
Sent via pgsql-performance mailing list (pgsql
All fields involved are declared NOT NULL, but thanks for the heads up.
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org
materialization, hence
getting rid of the biggest performance problem.
Thanks,
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org
somewhat better.
It looks like according to Andres, though, I should not be depending
on these plans with 8.3, so I may want to stick with the manual
antijoin.
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
43 matches
Mail list logo
