On Thu, Oct 12, 2017 at 11:50 PM, Tom Lane wrote:
> Ants Aasma writes:
>> I stumbled upon a severe row count underestimation that confusingly
>> went away when two inner joins in the from clause were reordered.
>
> Hm, looks more like an overestimate in this example, but
Does anybody have any idea what is going on here? In the real world
case this is based on the estimation was 5 rows instead of 200k, which
resulted in quite bad plan choices downstream.
Regards,
Ants Aasma
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To
]
http://www.postgresql.org/message-id/CA+CSw_tEpJ=md1zgxPkjH6CWDnTDft4gBi=+p9snoc+wy3p...@mail.gmail.com
Regards,
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de
--
Sent via pgsql-performance mailing list (pg
hout timing by using explain (analyze on, timing
off) select * from inventory;
Regards,
Ants Aasma
idea why is XLogInsert taking so much longer on 9.2.
[1] http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e6faf910
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de
--
Sent via pgsql-performance mailing li
izing 5GB of data will never be fast.
If you need that information quickly, you'll need to actively maintain
the aggregate values via triggers.
Regards,
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de
--
Sen
ing the root cause.
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
t)
> Total runtime: 10304211.648 ms
As you can see from the explain plan, postgresql is not using any
indexes here. The reason is the type mismatch between the X and x_min
columns. Use matching types between tables to enable index use. The
same goes for the id column, if the column type is
On Wed, Sep 26, 2012 at 11:11 PM, Jeff Janes wrote:
> On Wed, Sep 12, 2012 at 11:40 PM, Ants Aasma wrote:
>> I don't have any links for OS level monitoring, but with version 9.2
>> track_io_timing would do the job.
>
> I don't know how to advice people on how to u
ersion 9.2
track_io_timing would do the job.
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
whole index anyway. Because indexes are not stored in
logical order you don't get to benefit from sequential I/O.
The lazy merging approach (the paper that Claudio linked) on the other
hand seems promising but a lot trickier to implement.
Regards,
Ants Aasma
--
Cybertec Schönig & Sch
ferent execution plans, not estimate time taken. So it's completely
normal that it doesn't match actual time taken.
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de
--
Sent via pgsql-performance mailing list (pg
vide
better latency for trivial queries. And of course its always possible
to throw more hardware at the problem and upgrade the I/O subsystem.
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de
--
Sent via pgsql-perf
ages of other clusters that are
actually referenced.
Ants Aasma
rst explain on 8.4 was slow because the data was still on
disk. Raising work mem doubled the speed of the sort from 800ms to
400ms.
Regards,
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de
--
Sent via pgsql-perf
x-scans, or collecting multi-dimensional stats to figure
out the correlation that all rows for this user are likely to be old.
Regards,
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de
--
Sent via pgsql-performance ma
rchives.postgresql.org/message-id/ca+u5nmlbxfut9cwdhj3tpxjc3btwqizbkqtwdgzebcb5bag...@mail.gmail.com
Cheers,
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To
t good plans. As it happens
I'm working on adding this functionality to PostgreSQL and would love
to hear more details about your use-case to understand if it would be
solved by this work.
Regards,
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neu
it's worth checking for this very specific case because it
is a common idiom for Oracle users to implement constant false in
where predicates due to Oracle not allowing top level literal booleans
for some arcane reason or another.
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgass
stem has fast
timing. You can check the timing performance of your system with the
tool attached here:
http://archives.postgresql.org/message-id/4F15B930.50108%402ndQuadrant.com
Anything under 200ns should be ok.
Cheers,
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlga
f random sampling. Try raising your stats
target and re-analyzing to confirm.
All the best,
Ants Aasma
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
world cases). Unlogged tables are give me about 12k
tps which seems to confirm mostly CPU bound.
So regardless if the benchmark is a good representation of the target
workload or not, it definitely isn't benchmarking the IO system.
Ants Aasma
--
Sent via pgsql-performance mailing list (p
regate with
s1 = s1 + s2 and s1 = s1 + s2 - 1 correspondingly.
--
Ants Aasma
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
; it's not the same session.
Except that any open transactions are rolled back no other reset is done.
The correct way to handle this would be to set the options and commit the
transaction in Pool connect or checkout events. The event choice depends on
whether application scope or request scope
you can rewrite it as a semi-join
explicitly:
SELECT DISTINCT(user_id) FROM seen_its WHERE EXISTS (SELECT 1 FROM blocks
WHERE blocks.user_id = seen_its.user_id) AND seen_its.created BETWEEN
(now()::date - interval '8 days')::timestamp AND now()::date::timestamp
--
Ants Aasma
On Wed, Oct 13, 2010 at 10:35 PM, Mladen Gogala
wrote:
> You will see that for most of the columns, the length of the histogram array
> corresponds to the value of the default_statistics_target parameter. For
> those that are smaller, the size is the total number of values in the column
> in the s
I hit an issue with window aggregate costing while experimenting with
providing a count of the full match along side a limited result set.
Seems that the window aggregate node doesn't take into account that it
has to consume the whole input before outputting the first row. When
this is combined wit
27 matches
Mail list logo
