2017-11-15 20:58 GMT+01:00 Gunther :
>
> On 11/15/2017 8:12, Pavel Stehule wrote:
>
> There is wrong plan due wrong estimation
>
> for this query you should to penalize nested loop
>
> set enable_nestloop to off;
>
> before evaluation of this query
>
>
> Y
2017-11-15 13:54 GMT+01:00 Samir Magar :
> please find the EXPLAIN ANALYZE output.
>
> On Wed, Nov 15, 2017 at 3:13 PM, Pavel Stehule
> wrote:
>
>> Hi
>>
>> please send EXPLAIN ANALYZE output.
>>
>> Regards
>>
>> Pavel
>>
>> 2
Hi
please send EXPLAIN ANALYZE output.
Regards
Pavel
2017-11-15 10:33 GMT+01:00 Samir Magar :
> Hello,
> I am having performance issues with one of the query.
> The query is taking 39 min to fetch 3.5 mil records.
>
> I want to reduce that time to 15 mins.
> could you please suggest something
2017-10-11 18:52 GMT+02:00 Purav Chovatia :
> Yes, there is some code to catch exceptions like unique constraint
> violation and no data found. Do you suggest we trying by commenting that
> part? btw, the dataset is a controlled one, so what I can confirm is we are
> not hitting any exceptions.
>
2017-10-11 15:59 GMT+02:00 Purav Chovatia :
> Thanks Laurenz, am having a look at perf.
>
> Can you pls help understand what exactly do you mean when you say "PL/pgSQL
> is not optimized for performance like PL/SQL". Do you mean to indicate that
> app firing queries/DMLs directly would be a better
2017-10-11 13:06 GMT+02:00 johannes graën :
> Hi,
>
> I wrote a query that joins several tables usually returning less than
> 1000 rows, groups them and generates a JSON object of the result. In
> 9.6 is was a question of milliseconds for that query to return the
> requested data. Now, after upgra
2017-10-03 17:17 GMT+02:00 Adam Brusselback :
> There is also the option of pg_stat_statements: https://
> www.postgresql.org/docs/current/static/pgstatstatements.html and
> auto_explain: https://www.postgresql.org/docs/current/
> static/auto-explain.html
>
> These should help you identify what is
2017-09-21 12:52 GMT+02:00 Subramaniam C :
> Hi
>
> I wanted to query top 20 rows by joining two tables, one table having
> around 1 lac rows and other table having 5 lac rows. Since I am using ORDER
> BY in the query so I created compound index with the columns being used in
> ORDER BY. Initially
2017-08-15 18:13 GMT+02:00 Jeff Janes :
> On Tue, Aug 15, 2017 at 3:06 AM, Mariel Cherkassky <
> mariel.cherkas...@gmail.com> wrote:
>
>> Hi,
>> So I I run the cheks that jeff mentioned :
>> \copy (select * from oracle_remote_table) to /tmp/tmp with binary - 1
>> hour and 35 minutes
>> \copy local
>
> cab_id character varying,
>
> shift_key integer,
>
> roaster_creation_date date,
>
> status integer,
>
> notificationcount integer,
>
> totaltraveldistance double precision,
>
> start_trip text,
>
> end_trip text,
>
> trip_du
0 rows)
>
> I made a table dump for anyone who wants to give it a spin
> https://app.box.com/s/464b12glmlk5o4gvzz7krc4c8s2fxlwr
> and here is the gist for the original commands https://gist.github.
> com/lessless/33215d0c147645db721e74e07498ac53
>
> On Wed, Jun 28, 2017
gn_jobs" AS c0
WHERE ((c0."status" = $5) AND (c0."started_at" < $6))
ORDER BY c0."priority" DESC, c0."times_failed"
LIMIT $7
FOR UPDATE SKIP LOCKED
Something like this
Pavel
>
> On Wed, Jun 28, 2017 at 2:12 PM, Pavel Stehule
> wrote:
>
2017-06-28 8:47 GMT+02:00 Yevhenii Kurtov :
> Hello,
>
> We have a query that is run almost each second and it's very important to
> squeeze every other ms out of it. The query is:
>
> SELECT c0."id" FROM "campaign_jobs" AS c0
> WHERE (((c0."status" = $1) AND NOT (c0."id" = ANY($2
> OR ((c0."s
Hi
2017-05-22 22:21 GMT+02:00 Clemens Eisserer :
> Hi,
>
> I have a letancy-sensitive legacy application, where the time consumed
> by query planning was always causing some headaches.
> Currently it is running on postgresql-8.4 - will postgresql-10 support
> generating plans using multiple CPU c
2017-04-21 9:05 GMT+02:00 Marco Renzi :
>
>
>> I am thinking so limit 1 should be ok. Too big number can be messy
>> for optimizer similarly like too small number.
>>
>> The planner is driven by statistics - and the statistics are not perfect
>> - usually it is working on 80% - like weather fo
; SELECT fase.id
> FROMtipofase
> JOIN fase
> ON (fase.tipofase = tipofase.id)
> WHERE agendafrontoffice = true
> ORDER BYfase.id DESC limit 10 offset 0
> ) A
> ORDER BYA.id DESC limit 10 offset 0
>
> 2017-04-20 18:05 GMT+02
2017-04-20 17:57 GMT+02:00 Pavel Stehule :
>
>
> 2017-04-20 9:19 GMT+02:00 Marco Renzi :
>
>> Hi!, i've currently a big problem using ORBDER BY / LIMIT in a query
>> with no result set.
>> If i add the order by/limit clause it runs really re
2017-04-20 9:19 GMT+02:00 Marco Renzi :
> Hi!, i've currently a big problem using ORBDER BY / LIMIT in a query with
> no result set.
> If i add the order by/limit clause it runs really really slow.
>
>
>
> QUERY 1 FAST:
>
>
> SELECT fase.id
> FROMtipo
in some unrelational type - XML, JSON, ...
Regards
Pavel
>
> Thanks for the tips!
>
> On 23 February 2017 at 17:35, Pavel Stehule
> wrote:
>
>>
>>
>> 2017-02-23 15:02 GMT+01:00 Rowan Seymour :
>>
>>> Hi Pavel. That suggestion gets me as far as L
Pavel
> Btw I created the index values_value_field_string_value_contact as
>
> CREATE INDEX values_value_field_string_value_contact
> ON values_value(contact_field_id, UPPER(string_value), contact_id DESC)
> WHERE contact_field_id IS NOT NULL;
>
> I'm not sure why i
2017-02-23 14:11 GMT+01:00 Rowan Seymour :
> Hi guys
>
> I'm a bit stuck on a query that performs fantastically up to a certain
> limit value, after which the planner goes off in a completely different
> direction and performance gets dramatically worse. Am using Postgresql 9.3
>
> You can see all
--*
>
> Mobile: +91-9953975849 <+91%2099539%2075849> | Ext 1078
> |dinesh.chan...@cyient.com
>
> Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India.
>
>
>
> *From:* Pavel Stehule [mailto:pavel.steh...@gmail.com]
> *Sent:* 20 January, 2017 5:19 PM
>
transaction
segments.
or
2. buy faster IO
Regards
Pavel Stehule
>
> *Regards,*
>
> *Dinesh Chandra*
>
> *|Database administrator (Oracle/PostgreSQL)| Cyient Ltd. Noida.*
>
> *--*
>
> Mobile: +91-99539758
Hi
2017-01-02 15:34 GMT+01:00 Андрей Хозов :
> Hello there!
>
> I have an performance issue with functions and args type.
>
> Table and data:
> create table t1 (id serial, str char(32));
> insert into t1 (str) select md5(s::text) from generate_series(1, 100)
> as s;
>
> And simple functions:
2016-10-11 13:19 GMT+02:00 Andrzej Zawadzki :
> On 11.10.2016 03:47, Pavel Stehule wrote:
>
>
>
> 2016-10-10 23:17 GMT+02:00 Andrzej Zawadzki :
>
>> On 10.10.2016 17:31, Andrzej Zawadzki wrote:
>>
>> Hi,
>> Today, I noticed strange situation:
>>
>
2016-10-10 23:17 GMT+02:00 Andrzej Zawadzki :
> On 10.10.2016 17:31, Andrzej Zawadzki wrote:
>
> Hi,
> Today, I noticed strange situation:
>
> The same query run on different servers has very different plan:
>
> Q: SELECT b.* FROM kredytob b WHERE pesel = '222' ORDER BY b.id
> DESC LIMIT
2016-10-10 17:31 GMT+02:00 Andrzej Zawadzki :
> Hi,
> Today, I noticed strange situation:
>
> The same query run on different servers has very different plan:
>
> Q: SELECT b.* FROM kredytob b WHERE pesel = '222' ORDER BY b.id
> DESC LIMIT 1
>
> Slow plan:
>
> "Limit (cost=0.43..28712.3
2016-09-29 20:49 GMT+02:00 Sven R. Kunze :
> On 29.09.2016 20:12, Pavel Stehule wrote:
>
>> In ideal world then plan should be independent on used form. The most
>> difficult is safe estimation of OR predicates. With correct estimation the
>> transformation to UNION form s
2016-09-29 14:20 GMT+02:00 Sven R. Kunze :
> On 23.09.2016 11:00, Pavel Stehule wrote:
>
> 2016-09-23 8:35 GMT+02:00 Sven R. Kunze :
>
>> I was wondering: would it be possible for PostgreSQL to rewrite the query
>> to generate the UNION (or subquery plan if it's also
2016-09-26 17:39 GMT+02:00 dby...@163.com :
> test:
> create type h3 as (id int,name char(10));
>
> CREATE or replace FUNCTION proc17()
> RETURNS SETOF h3 AS $$
> DECLARE
> v_rec h3;
> BEGIN
> create temp table abc(id int,name varchar) on commit drop;
> insert into abc select 1,'lw';
> inser
2016-08-26 22:26 GMT+02:00 Mike Sofen :
>
>
> *From:* pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-
> ow...@postgresql.org] *On Behalf Of *Tommi K
> *Sent:* Friday, August 26, 2016 7:25 AM
> *To:* Craig James
> *Cc:* andreas kretschmer ;
> pgsql-performance@postgresql.org
> *S
2016-08-20 14:17 GMT+02:00 :
> On 2016-08-20 12:05, Pavel Stehule wrote:
>
>> 2016-08-20 13:59 GMT+02:00 :
>>
>> On 2016-08-20 11:42, Pavel Stehule wrote:
>>>
>>> 2016-08-20 13:31 GMT+02:00 :
>>>
>>> On 2016-08-20 08:58, Pavel Stehule w
2016-08-20 13:59 GMT+02:00 :
> On 2016-08-20 11:42, Pavel Stehule wrote:
>
>> 2016-08-20 13:31 GMT+02:00 :
>>
>> On 2016-08-20 08:58, Pavel Stehule wrote:
>>> 2016-08-20 10:27 GMT+02:00 :
>>>
>>> On 2016-08-20 08:21, pgsql-performance-ow...
2016-08-20 13:31 GMT+02:00 :
> On 2016-08-20 08:58, Pavel Stehule wrote:
>
>> 2016-08-20 10:27 GMT+02:00 :
>>
>> On 2016-08-20 08:21, pgsql-performance-ow...@postgresql.org wrote:
>>>
>>> Welcome to the pgsql-performance mailing list!
>>
2016-08-20 10:27 GMT+02:00 :
> On 2016-08-20 08:21, pgsql-performance-ow...@postgresql.org wrote:
>
>> Welcome to the pgsql-performance mailing list!
>> Your password at PostgreSQL Mailing Lists is
>>
>> x8DiA6
>>
>> To leave this mailing list, send the following command in the body
>> of a messag
Hi
2016-08-10 13:13 GMT+02:00 Ivan Voras :
> Hello,
>
> Is it possible to log queries using sequential scans? Or possibly every
> query in a way which allows grepping for those with sequential scans?
>
>
>
you can log execution plan with auto_explain extension
https://www.postgresql.org/docs/cu
2016-04-04 16:43 GMT+02:00 Moreno Andreo :
> Il 04/04/2016 15:33, Pavel Stehule ha scritto:
>
>>
>>
>> PostgreSQL doesn't contain integrated pooler - so any connection to
>> Postgres enforces one PostgreSQL proces. A performance benchmarks is
>> showing m
Hi
2016-04-04 15:14 GMT+02:00 Mike Sofen :
> From: Jim Nasby Sent: Sunday, April 03, 2016 10:19 AM
>
> >>On 4/1/16 2:54 AM, jarek wrote:
> >> I'll be happy to hear form users of big PostgreSQL installations, how
> >> many users do you have and what kind of problems we may expect.
> >> Is there an
2016-03-16 21:23 GMT+01:00 Doiron, Daniel :
> I have the following queries:
>
> EXPLAIN (ANALYZE, VERBOSE, COSTS, BUFFERS, TIMING)
> select[…]
> from f_calc_service a11,
> d_patient_typea12
> where a11.d_p
Hi
2016-03-02 16:25 GMT+01:00 Artem Tomyuk :
> Hi.
>
> I've noticed that autovac. process worked more than 10 minutes, during
> this zabbix logged more than 90% IO disk utilization on db volume
>
> ===>29237 2016-03-02 15:17:23 EET 0 [24-1]LOG: automatic
> vacuum of table "lb_
Hi
> I ran operf on both backends, and they look quite similar, except that the
> number of samples is different (this is "opreport -c" output):
>
> CPU: Intel Sandy Bridge microarchitecture, speed 2899.8 MHz (estimated)
> Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a uni
Hi
> Does anyone have any ideas? All data are loaded into this table via copy
> and no updates are done. Autovacuum settings weren't changed (and is on
> both). Do I need to increase shared_buffers to half of available memory for
> the planner to make certain optimisations? Anything else I'm miss
t;
>
> On Wednesday, 23 December 2015 8:04 AM, Jim Nasby
> wrote:
>
>
> On 12/22/15 2:09 AM, Pavel Stehule wrote:
>
> >
> > There was lot of bugfix releases after 9.1.2 - currently there is
> > PostgreSQL 9.2.19.
>
>
> I'm sure Pavel meant 9.1.19, not
Hi
2015-12-22 8:59 GMT+01:00 Artem Tomyuk :
> Hi.
>
> I've noticed huge decrease in performance.
> During this in htop i see a lot (200 - 300) of connections in state
> "startup", each of them eats 3-3% of CPU time. This processes are not
> visible in pg_stat_activity so i cant understand what th
Hi
2015-12-18 16:21 GMT+01:00 Mathieu VINCENT :
> Hello,
>
> No one to help me to understand this bad estimation rows ?
> It's *NOT* caused by :
>
>- correlation between columns (cross-correlation)
>- bad statistics (i tried with default_statistics_target to 10 000)
>- bad number of
ostgres 20 0 65.7g 68m 64m R69 0.0 0:00.55 postgres:
> user1 db 0.0.0.3(44814) SELECT
> 82715 postgres 20 0 65.7g 75m 70m R69 0.0 0:00.58 postgres:
> user1 db 0.0.0.4(49905) SELECT
> 19548 postgres 20 0 65.7g 79m 56m R65 0.0 8:02.44 postgres:
>
g is
good fro production usage, but the result can be interesting for bottleneck
identification.
>
>
> *From:* Pavel Stehule [mailto:pavel.steh...@gmail.com]
> *Sent:* Wednesday, October 21, 2015 12:26 PM
>
> *To:* Jamie Koceniak
> *Cc:* pgsql-performance@postgresql.o
2015-10-21 20:51 GMT+02:00 Jamie Koceniak :
> Hi Pavel,
>
>
>
> Or were you referring to SHMMAX?
>
value of shared_buffers - run SQL statements SHOW shared_buffers;
Regards
Pavel
>
>
> Thanks
>
>
>
> *From:* Jamie Koceniak
> *Sent:* Wednesday, Octobe
size of shared memory? Probably is significantly lower than
effective_cache_size? Try to reduce effective cache size to be lower than
shared buffers
Regards
Pavel
>
>
> Thanks,
>
> Jamie
>
>
>
> *From:* Pavel Stehule [mailto:pavel.steh...@gmail.com]
> *Sent:* Wednesday, October 21
Hi
2015-10-20 19:34 GMT+02:00 Jamie Koceniak :
> Version:
>
>
> ---
>
> PostgreSQL 9.1.14 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
> 4.7.2-5) 4.7.2, 64-bit
>
>
>
> Query Plan
>
> http://expla
2015-10-20 16:48 GMT+02:00 Jonathan Rogers :
> On 10/20/2015 03:45 AM, Pavel Stehule wrote:
> >
> >
> > 2015-10-20 8:55 GMT+02:00 Thomas Kellerer > <mailto:spam_ea...@gmx.net>>:
> >
> > Jonathan Rogers schrieb am 17.10.2015 um 04:14:
> >
2015-10-20 8:55 GMT+02:00 Thomas Kellerer :
> Jonathan Rogers schrieb am 17.10.2015 um 04:14:
> >>> Yes, I have been looking at both plans and can see where they diverge.
> >>> How could I go about figuring out why Postgres fails to see the large
> >>> difference in plan execution time? I use exac
2015-10-17 15:29 GMT+02:00 Yves Dorfsman :
> On 2015-10-14 03:00, Albe Laurenz wrote:
> >
> > You are encountering "custom plans", introduced in 9.2.
> >
> > When a statement with parameters is executed, PostgreSQL will not only
> generate
> > a generic plan, but for the first 5 executions it will
2015-10-17 4:29 GMT+02:00 Jonathan Rogers :
> On 10/14/2015 05:01 AM, Pavel Stehule wrote:
> > Hi
> >
> > 2015-10-14 9:38 GMT+02:00 Jonathan Rogers > <mailto:jrog...@socialserve.com>>:
> >
> > I have a very complex SELECT for which I use PREPA
Hi
2015-10-14 9:38 GMT+02:00 Jonathan Rogers :
> I have a very complex SELECT for which I use PREPARE and then EXECUTE.
> The first five times I run "explain (analyze, buffers) execute ..." in
> psql, it takes about 1s. Starting with the sixth execution, the plan
> changes and execution time doub
d even though I have, I need to
> assume that I don’t have the control over this one and that I’m using it as
> if.
>
>
>
> This is only my debugging query.
>
>
>
> Best regards,
>
>
>
> Kevin
>
>
>
> *From:* Pavel Stehule [mailto:pavel.steh...
Hi
long CASE can be problem. Why you don't use a dictionary table and join?
Regards
Pavel
2015-03-31 10:53 GMT+02:00 Kevin Viraud :
> Hi,
>
>
>
> I have an issue with a rather large CASE WHEN and I cannot figure out why
> it is so slow...
>
>
>
> First, here is my test query :
>
>
>
> SELECT
Hi
what is your random_page_cost and seq_page_cost?
Regards
Pavel Stehule
2015-03-19 7:23 GMT+01:00 Jake Magner :
> I am having problems with a join where the planner picks a merge join and
> an
> index scan on one of the tables. Manually disabling merge joins and running
> th
Hi
2015-03-18 20:07 GMT+01:00 Vivekanand Joshi :
> Hi Team,
>
>
>
> I don't know under which section does this question comes, so I am posting
> this question to both Admin and performance mailing list. Apologies in
> advance.
>
>
>
> Objective:
>
>
>
> We are planning to use PostgreSQL instead o
2015-03-12 1:35 GMT+01:00 atxcanadian :
> So I implemented two changes.
>
> - Moved random_page_cost from 1.1 to 2.0
>
random_page_cost 1 can enforce nested_loop - it is very cheap with it
> - Manually ran analyze on all the tables
>
> *Here is the new explain analyze:*
> QUERY PLAN
> HashAggre
Hi
2015-02-09 12:22 GMT+01:00 sridhar bamandlapally :
> Hi All
>
> We are testing our Oracle compatible business applications on PostgreSQL
> database,
>
> the issue we are facing is Vs NULL
>
> In Oracle '' () and NULL are treated as NULL
>
> but, in PostgreSQL '' not treated as NULL
>
> I nee
2015-02-06 14:39 GMT+01:00 Belal Al-Hamed :
> Let me change my question to this perhaps it would be clearer
>
> why writing data result of select statment from PG server to file on disk
> using copy statement is much faster than getting same data through PGAdmin
> via libpg on the same PC on the s
2015-02-06 10:50 GMT+01:00 Belal Al-Hamed :
> fast as
>
> Query returned successfully: 43602 rows affected, 1089 ms execution time.
>
so bottle neck have to be some where between client and server
Pavel
>
>
>
> --
> View this message in context:
> http://postgresql.nabble.com/Copy-command-Fas
2015-02-06 10:15 GMT+01:00 Belal Al-Hamed :
> "this slowdown can be enforced by slow client (or slow network)."
> As I said i made the tow test on the same machine as the server using
> PGAdmin no network involved.
>
> "pgAdmin is not terrible fast"
> I also try the same query from my application
2015-02-06 9:44 GMT+01:00 Belal Al-Hamed :
> thanks,
>
> but isn't copy use the same plan ???
>
aha - I was wrong,
this slowdown can be enforced by slow client (or slow network). pgAdmin is
not terrible fast. Try to execute your query from psql.
Regards
Pavel
>
> any way this is the query pl
Hi
2015-02-06 9:30 GMT+01:00 belal :
> I made complex select using PGAdmin III Query Editor, Postgre server 9.3
>
>
> select ... from mytable join .. join ... order by
>
> I get [Total query runtime: 8841 ms. 43602 rows retrieved.]
>
> but when I use
>
> copy ([same above select]) to '/x.txt
2015-01-31 2:40 GMT+01:00 Jim Nasby :
> On 1/25/15 2:03 AM, Pavel Stehule wrote:
>
>> It might not always be an integer, just happens to be so here.
>> Should I try text instead? I don't have to have the case-insensitive
>> matching.
>>
>>
&g
2015-01-25 8:20 GMT+01:00 Joe Van Dyk :
> On Sat, Jan 24, 2015 at 11:14 PM, Pavel Stehule
> wrote:
>
>>
>>
>> 2015-01-25 7:38 GMT+01:00 Joe Van Dyk :
>>
>>>
>>>
>>> On Sat, Jan 24, 2015 at 10:12 PM, Pavel Stehule >> >
2015-01-25 7:38 GMT+01:00 Joe Van Dyk :
>
>
> On Sat, Jan 24, 2015 at 10:12 PM, Pavel Stehule
> wrote:
>
>> Hi
>>
>> this plan looks well
>>
>> Regards
>>
>> Pavel
>>
>
> Here's one that's not quite as well: h
Hi
this plan looks well
Regards
Pavel
2015-01-25 6:45 GMT+01:00 Joe Van Dyk :
> Oops, didn't run vacuum analyze after deleting the events. Here is another
> 'explain analyze': http://explain.depesz.com/s/AviN
>
> On Sat, Jan 24, 2015 at 9:43 PM, Joe Van Dyk wrote:
>
>> On Sat, Jan 24, 2015 at
2014-09-19 13:51 GMT+02:00 Björn Wittich :
> Hi mailing list,
>
> I am relatively new to postgres. I have a table with 500 coulmns and about
> 40 mio rows. I call this cache table where one column is a unique key
> (indexed) and the 499 columns (type integer) are some values belonging to
> this ke
don’t quite get why…
>
These values can be messy -- timing in EXPLAIN ANALYZE has relative big
impact but different for some methods
try to watch complete time for EXPLAIN (ANALYZE, TIMING OFF)
>
>
> Thanks,
>
> Suya
>
>
>
> *From:* Pavel Stehule [mailto:pavel.steh
Hi
In this use case hstore should not help .. there is relative high overhead
related with unpacking hstore -- so classic schema is better.
Hstore should not to replace well normalized schema - it should be a
replace for some semi normalized structures as EAV.
Hstore can have some profit from TO
2014-06-30 20:34 GMT+02:00 Jeff Frost :
> On Jun 30, 2014, at 10:29 AM, Soni M wrote:
>
>
>
>
> On Tue, Jul 1, 2014 at 12:14 AM, Andres Freund
> wrote:
>
>>
>> My guess it's a spinlock, probably xlogctl->info_lck via
>> RecoveryInProgress(). Unfortunately inline assembler doesn't always seem
>>
2014-06-20 1:44 GMT+02:00 Huang, Suya :
>
>
> From: Pavel Stehule [mailto:pavel.steh...@gmail.com]
> Sent: Thursday, June 19, 2014 3:41 PM
> To: Huang, Suya
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24
>
>
&g
2014-06-19 7:35 GMT+02:00 Huang, Suya :
> From: Pavel Stehule [mailto:pavel.steh...@gmail.com]
> Sent: Thursday, June 19, 2014 3:28 PM
> To: Huang, Suya
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24
>
> Hello
>
&
Hello
The size of statfile is related to size of database objects in database.
Depends on PostgreSQL version this file can be one per database cluster or
one per database (from 9.3),
These statistics should by reset by call pg_stat_reset()
http://www.postgresql.org/docs/9.2/static/monitoring-sta
Dne 23.5.2014 16:41 "Dimitris Karampinas" napsal(a):
>
> Thanks for your answers. A script around pstack worked for me.
>
> (I'm not sure if I should open a new thread, I hope it's OK to ask
another question here)
>
> For the workload I run it seems that PostgreSQL scales with the number of
concur
Hello
we had similar issue - you can try to move statfile to ramdisc
http://serverfault.com/questions/495057/too-much-i-o-generated-by-postgres-stats-collector-process
Regards
Pavel Stehule
2014-05-15 6:18 GMT+02:00 Craig James :
> Day and night, the postgres stats collector process runs
2014-05-01 22:30 GMT+02:00 Andreas Joseph Krogh :
> På torsdag 01. mai 2014 kl. 21:53:32, skrev Pavel Stehule <
> pavel.steh...@gmail.com>:
>
>
>
> 2014-05-01 21:39 GMT+02:00 Andreas Joseph Krogh :
>>
>> På torsdag 01. mai 2014 kl. 21:30:39, skrev Pavel
2014-05-01 21:39 GMT+02:00 Andreas Joseph Krogh :
> På torsdag 01. mai 2014 kl. 21:30:39, skrev Pavel Stehule <
> pavel.steh...@gmail.com>:
>
> Hello
> [snip]
>
> I had a perfect success on similar use case with descent ordered partial
> index
>
> http://www
Hello
2014-05-01 21:17 GMT+02:00 Andreas Joseph Krogh :
> På torsdag 01. mai 2014 kl. 20:35:07, skrev Jochem Berndsen <
> joc...@functor.nl>:
>
>
> Hi Andreas,
>
> [New to this list, forgive my ignorance.]
> [snip]
> I'm getting better performance with:
>
> SELECT
> m.id AS message_id,
> 1 AS pe
out 10 x CPU connections. But it highly
depends on load.
Regards
Pavel Stehule
>
> Thanks and Best ergards.
>
t; Select count(*) from measurement this_ where this_.logdate between
> '2007-12-19 23:38:41.22'::timestamp and '2007-12-20 08:01:04.22'::timestamp
> and this_.city_id=25183;
>
> count
> --
> 312046
>
> Total Rows in the partition table referenced
> --
>
> Select count(*) from measurement_y2007m12;
>
> count
> -
> 38261732
>
>
>
>
> *Does anyone know how to speed up this query? I removed the order by
> clause and that significantly reduced the run time to approx. 2000-3000 ms.
> This query is being recorded repeatedly in our logs and executes very
> slowly for our UI users from 12000 ms thru 68000 msAny suggestions would be
> appreciated.*
>
sort (ORDER BY clause) enforce a reading of complete partitions. And it is
slow - it is strange so reading 300K rows needs a 5K sec. Probably your IO
is overloaded.
Regards
Pavel Stehule
>
> thanks
>
to off; -- for this query
* divide this query to more queries - store result temporary table and
analyze (fix wrong estimation)
* maybe you can increase a work_mem
Regards
Pavel Stehule
correlation between columns) a statistics estimations are totally out. And
bad musty statistics can produces better estimations than fresh statistics
please send a "EXPLAIN ANALYZE" output for fast and slow queries.
Regards
Pavel Stehule
>
>
> Thanks a lot for your help!
>
>
>
> Katharina
>
>
>
>
>
les only for 10 rows in result where
> conditions are very good ... is strange
>
>
Maybe index is not in good form
try to build index with varchar_pattern_ops flag
http://postgres.cz/wiki/PostgreSQL_SQL_Tricks_I#LIKE_optimalization
CREATE INDEX like_index ON people(surname varchar_patt
2013/12/6 Josh Berkus
> On 12/05/2013 02:42 AM, Max wrote:
> > Hello,
> >
> > We are starting a new project to deploy a solution in cloud with the
> possibility to be used for 2.000+ clients. Each of this clients will use
> several tables to store their information (our model has about 500+ table
Hello
2013/10/1 Xenofon Papadopoulos
> If we reset the statistics counters using pg_stat_reset() will it affect
> the performance of the database? Eg are these the same statistics used by
> the planner?
> Thanks
>
>
these statistics are used only for autovacuum, what I know. So you can
impact a
han I can
expect.
What I know - a usual advice for MS Win is setting minimal shared bufferes
- 512MB can be too much there.
Regards
Pavel Stehule
2013/8/26 Adam Ma'ruf
> Hi,
>
> I wasn't whether or not to mail to the novice mailing list of this one.
> Since this is perform
2013/8/16 Александр Белинский
> 12.08.2013 18:24, Pavel Stehule пишет:
>
> Hello
>>
>> it looks like known issue of sometimes dysfunctional plan cache in
>> plpgsql in 9.2.
>>
>> similar issuehttp://postgresql.**1045698.n5.nabble.com/**
>> Perform
2013/8/14 Craig Ringer
> Hi folks
>
> I've run into an interesting Stack Overflow post where the user shows
> that marking a particular function as IMMUTABLE significantly hurts the
> performance of a query.
>
> http://stackoverflow.com/q/18220761/398670
>
> CREATE OR REPLACE FUNCTION
> to_date
Hello
it looks like known issue of sometimes dysfunctional plan cache in
plpgsql in 9.2.
similar issue
http://postgresql.1045698.n5.nabble.com/Performance-problem-in-PLPgSQL-td5764796.html
Regards
Pavel Stehule
2013/8/12 Александр Белинский :
> Hi!
> I can't explain why function i
, but I'm certain I copied
> it all.
what is time of EXPLAIN only ?
Pavel
>
>
>
> I did this via pgadmin, but that shouldn't matter, should it?
>
>
>
> Thank you,
>
>
>
> Peter Slapansky
>
> _________
2013/8/7 Igor Neyman :
>
>
> From: pgsql-performance-ow...@postgresql.org
> [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of sl...@centrum.sk
> Sent: Wednesday, August 07, 2013 8:43 AM
> To: Pavel Stehule
> Cc: pgsql-performance@postgresql.org
> Subject: [PE
Hello
please, send result of EXPLAIN ANALYZE
please, use a http://explain.depesz.com/ for saving a plan
there is a more than 8 joins - so try to set geqo_threshold to 16,
join_collapse_limit to 16, and from_collapse_limit to 16.
Regards
Pavel Stehule
2013/8/2 :
> Good day,
>
>
Hello
2013/7/19 Skarsol :
> I tried sending this a couple days ago but I wasn't a member of the group so
> I think it's in limbo. Apologies if a 2nd copy shows up at some point.
>
> We recently migrated a 1.3TB database from 8.4 to 9.2.2 on a new server. As
> part of this migration we added part
2013/3/18 Kevin Grittner :
> Rodrigo Barboza wrote:
>
>> So setting this as half of ram, as suggested in postgres tuning
>> webpage should be safe?
>
> Half of RAM is likely to be a very bad setting for any work load.
> It will tend to result in the highest possible number of pages
> duplicated in
2013/3/18 Rodrigo Barboza :
> So setting this as half of ram, as suggested in postgres tuning webpage
> should be safe?
> It says it is a conservative value...
depends how much memory is used as cache ??
it can be a shared_buffers + file system cache
Regards
Pavel Stehule
>
>
&
2013/3/18 Pavel Stehule :
> Hello
>
> 2013/3/18 Rodrigo Barboza :
>> Hi guys, I am worried about the effective_cache_size.
>> I run a 32-bits postgres installation on a machine with 64 bits kernel.
>> Should I limit effective_cache_size to a maximum of 2.5gb?
>
>
1 - 100 of 253 matches
Mail list logo
