NOT IN (SELECT cqc.b
FROM bar cqc) AND b NOT IN (SELECT car.b FROM foo car WHERE car.type
!= 'o');
EXPLAIN DELETE FROM foo WHERE b IN (SELECT candidate_run.type_o_run as
b FROM (SELECT cqar1.b AS type_o_run, cqar2.b AS non_type_o_run FROM
foo cqar1 LEFT OUTER JOIN foo cqar2 ON (cqar1.b = c
faster than the original. Since 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-
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)
To
OT EXISTS) prevents 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-perform
par with the antijoin in 8.3,
but significantly better in 8.4). In 8.4, this EXCEPT version does
seem 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
the ORDER BY happens 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 sugges
iors that users have been taking for
granted.
---
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 subs
> 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
yellow pages. If you want 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
either push- or
pull-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.
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
pg_dump explicitly
(I 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 S
>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
> 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 |
;t shut down with this command, maybe you should try
something else", and not "I couldn't shut down right now, although
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
) only
hints at this, and pg_ctl --help does not really mention this at all.
Of the two other issues, (3) 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
What exactly is the problem? Is one version of this plan slow? Which
one? If you 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,
ld erroneously 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@postgresq
, you should optimize your settings to get this plan
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. Hillsdal
> 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)
is really just a guess. Also remember
to ensure that stats are up 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
> 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
missing).
Other than that, I think Shaun's comments 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 change
> 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-performa
Sort can't really operate in a "streaming" 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 | S
you
> have the energy for it.
Another +1. 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
&
> (tcp-syn|tcp-fin) != 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 conn
e
copy), although I don't think 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 | Syst
> 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 C
> 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 port
On Wed, Jul 11, 2012 at 5:47 PM, Yan Chunlu 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
connections[1]: consider shrin
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 proble
> 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 stil
On Wed, Jul 25, 2012 at 7:13 PM, Rural Hunter 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 col2='"+col
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
--
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 non
On Wed, Oct 17, 2012 at 1:53 AM, Martin French
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 (i.e the old
> box) to ge
On Wed, Oct 17, 2012 at 7:18 AM, Tom Lane 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 lock table.
>
On Tue, Oct 30, 2012 at 2:24 AM, vignesh 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 could be considered a pe
On Fri, Feb 22, 2013 at 9:59 AM, Vitalii Tymchyshyn 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
> to have
On Sat, Feb 23, 2013 at 3:53 PM, Jeff Janes 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 now, although g
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, whi
On Thu, Jun 20, 2013 at 9:13 PM, bricklen wrote:
>
> On Thu, Jun 20, 2013 at 6:24 PM, Maciek Sakrejda wrote:
>
>> SELECT
>> DISTINCT ON (type) ts, type, details
>> FROM
>> observations
>> WHERE
>> subject = '...'
>> ORDER BY
&
On Fri, Jun 21, 2013 at 9:08 AM, bricklen 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 what I was missin
44 matches
Mail list logo
