Re: [PERFORM] Explain Analyze - actual time in loops

inutes? ……. thank you and best regards [] 's Neto Neto, The time you see there is in ms, so the point (‘.’) you see is the digital point. So, it is 419.113ms or a little less than half a second (0.419sec). Igor Neyman

Re: [PERFORM] performance problem on big tables

ltering on Oracles side and just push the result set to Postgres. That’s how I did migration from Oracle to Postgres. Regards, Igor Neyman

Re: [PERFORM] Very poor read performance, query independent

From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Igor Neyman Sent: Friday, July 14, 2017 3:13 PM To: Charles Nadeau Cc: Jeff Janes ; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Very poor read performance, query independent

Re: [PERFORM] Very poor read performance, query independent

From: Charles Nadeau [mailto:charles.nad...@gmail.com] Sent: Friday, July 14, 2017 11:35 AM To: Igor Neyman Cc: Jeff Janes ; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Very poor read performance, query independent Igor, Initially temp_buffer was left to its default value (8MB

Re: [PERFORM] Very poor read performance, query independent

From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Charles Nadeau Sent: Wednesday, July 12, 2017 6:05 AM To: Jeff Janes mailto:jeff.ja...@gmail.com>> Cc: pgsql-performance@postgresql.org

Re: [PERFORM] Very poor read performance, query independent

From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Charles Nadeau Sent: Wednesday, July 12, 2017 6:05 AM To: Jeff Janes Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Very poor read performance, query independent flows=# expla

Re: [PERFORM] Very poor read performance, query independent

From: Charles Nadeau [mailto:charles.nad...@gmail.com] Sent: Wednesday, July 12, 2017 3:21 AM To: Igor Neyman Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Very poor read performance, query independent Igor, I set shared_buffers to 24 GB and effective_cache_size to 64GB and I can

Re: [PERFORM] Very poor read performance, query independent

From: Charles Nadeau [mailto:charles.nad...@gmail.com] Sent: Tuesday, July 11, 2017 11:25 AM To: Igor Neyman Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Very poor read performance, query independent Attention: This email was sent from someone outside of Perceptron. Always

Re: [PERFORM] Very poor read performance, query independent

From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Igor Neyman Sent: Tuesday, July 11, 2017 10:34 AM To: Charles Nadeau Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Very poor read performance, query independent From: Charles

Re: [PERFORM] Very poor read performance, query independent

From: Charles Nadeau [mailto:charles.nad...@gmail.com] Sent: Tuesday, July 11, 2017 6:43 AM To: Igor Neyman Cc: Andreas Kretschmer ; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Very poor read performance, query independent Igor, I reduced the value of random_page_cost to 4 but the

Re: [PERFORM] Very poor read performance, query independent

running on the server). Regards, Igor Neyman

Re: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause

-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Igor Neyman Sent: Thursday, September 22, 2016 10:36 AM To: Sven R. Kunze ; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Multiple-Table-Spanning Joins

Re: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause

-Original Message- From: Igor Neyman Sent: Thursday, September 22, 2016 10:33 AM To: 'Sven R. Kunze' ; pgsql-performance@postgresql.org Subject: RE: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause -Original Message- From: pgsql-performance-ow...@post

Re: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause

QL. But how? Best, Sven PS: if you require EXPLAIN ANALYZE, I can post them as well. ______ What about: SELECT FROM "big_table" INNER JOIN "table_a" ON ("big_table"."id&quo

Re: [PERFORM] Problem with performance using query with unnest after migrating from V9.1 to V9.2 and higher

treubuch IT GmbH _____ table_a is too small, just 50 records. Optimizer decided (correctly) that Seq Scan is cheaper than using an index. Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Random slow queries

-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of devel.brai...@xoxy.net Sent: Tuesday, June 28, 2016 9:24 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] Random slow queries Hi, I have a weird slow q

Re: [PERFORM] Big number of connections

mance ? I'll be happy to hear any remarks and suggestions related to design, administration and handling of such installation. best regards Jarek ___ Take a look at PgBouncer. It should solve your problems. Regards, I

Re: [PERFORM] Primary key index partially used

From: Igor Neyman Sent: Tuesday, January 26, 2016 11:01 AM To: 'Florian Gossin' ; pgsql-performance@postgresql.org Subject: RE: [PERFORM] Primary key index partially used From: pgsql-performance-ow...@postgresql.org<mailto:pgsql-performance-ow...@postgresql.org> [mailto:pgsq

Re: [PERFORM] Primary key index partially used

st, It’s a god (for performance) practice to create indexes on FK columns in “child” table. Second, PG is using index only if the first column in concatenated index is used in WHERE clause. That is exactly what you observe. Regards, Igor Neyman

Re: [PERFORM] Slow 3 Table Join with v bad row estimate

the same table which is already being scanned. Thanks for this! David, I believe the plan you are posting is the old plan. Could you please post explain analyze with the index that Tom suggested? Regards, Igor Neyman

Re: [PERFORM] One long transaction or multiple short transactions?

normal connections has the same positive effect. Regards, Igor Neyman

Re: [PERFORM] shared-buffers set to 24GB but the RAM only use 4-5 GB average

-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Igor Neyman Sent: Monday, October 05, 2015 2:25 PM To: FattahRozzaq ; pgsql-performance@postgresql.org Subject: Re: [PERFORM] shared-buffers set to 24GB but the

Re: [PERFORM] shared-buffers set to 24GB but the RAM only use 4-5 GB average

ly utilizes the resource of RAM for max_connections? Thanks, FattahRozzaq Why are you looking at memory consumption? Are you experiencing performance problems? Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Server slowing down over time

ease let me know if any other information may be useful. Jean Cavallo Having 4 CPUs, I’d try to decrease number of connections from ~20 to 8, and see if “slowing down” still happens. Regards, Igor Neyman

Re: [PERFORM] Index creation running now for 14 hours

From: Tory M Blue [mailto:tmb...@gmail.com] Sent: Wednesday, August 26, 2015 3:26 PM To: Igor Neyman Cc: pgsql-performance Subject: Re: [PERFORM] Index creation running now for 14 hours On Wed, Aug 26, 2015 at 12:18 PM, Igor Neyman mailto:iney...@perceptron.com>> wrote: From:

Re: [PERFORM] Index creation running now for 14 hours

ice:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 1.50 0.00 344.00 0688 sdb 0.00 0.00 0.00 0 0 Check pg_locks in regards to the table in question. Regards, Igor Neyman

Re: [PERFORM] Performance bottleneck due to array manipulation

what you are trying to accomplish? Regards, Igor Neyman

Re: [PERFORM] Slow query (planner insisting on using 'external merge' sort type)

-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Igor Neyman Sent: Friday, June 19, 2015 11:07 AM To: Ian Pushee; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Slow query (planner insisting on using

Re: [PERFORM] Slow query (planner insisting on using 'external merge' sort type)

merge' sort type) On 6/19/2015 10:46 AM, Igor Neyman wrote: > > Probably events_confidnce index is not very selective, that's why optimizer > prefers seq scan. > I'd try to create an index on (name, eventspy_id, camera_id, type, status). > > Also, the re

Re: [PERFORM] Slow query (planner insisting on using 'external merge' sort type)

ably events_confidnce index is not very selective, that's why optimizer prefers seq scan. I'd try to create an index on (name, eventspy_id, camera_id, type, status). Also, the recent 9.2 is 9.2.13, you should upgrade. Regards, Igor Neyman -- Sent via pgsql-performance mailing list (p

Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)

t require much additional resource, but will eliminate some network traffic that you have with the current configuration. Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] How to calculate statistics for one column

From: Irineu Ruiz [mailto:iri...@rassystem.com.br] Sent: Thursday, June 18, 2015 3:10 PM To: Igor Neyman Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] How to calculate statistics for one column I din't understood. In this case, my statistics target should be approximately 3

Re: [PERFORM] How to calculate statistics for one column

From: Irineu Ruiz [mailto:iri...@rassystem.com.br] Sent: Thursday, June 18, 2015 2:18 PM To: Igor Neyman Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] How to calculate statistics for one column SELECT COUNT(DISTINCT id_camada) FROM … equals 349 And it doesn't change signific

Re: [PERFORM] How to calculate statistics for one column

over time? Regards, Igor Neyman

[PERFORM] Re: Query running slow for only one specific id. (Postgres 9.3) version

From: Sheena, Prabhjot [mailto:prabhjot.si...@classmates.com] Sent: Friday, June 05, 2015 2:38 PM To: Igor Neyman; pgsql-gene...@postgresql.org; pgsql-performance@postgresql.org Subject: RE: Query running slow for only one specific id. (Postgres 9.3) version When I run vacuum analyze it fixes

[PERFORM] Re: Query running slow for only one specific id. (Postgres 9.3) version

cords while really getting only 1. Regards, Igor Neyman

Re: [PERFORM] Poor performance when deleting from entity-attribute-value type master-table

r for constraint ... I have lots of tables referencing onp_crm_entity(entity_id) so I expect the poor performance of deleting from it is caused by all the triggers firing to check FKI-constraints. Andreas, do you have indexes on FK columns in child tables? If not – there is your problem. Regards, Igor Neyman

Re: [PERFORM] Copy command Faster than original select

time to display all these rows, so you'll get the same (or better) performance as with "copy" into text file, which will prove this theory. Regards, Igor Neyman -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.or

Re: [PERFORM] extremly bad select performance on huge table

>> >> regards, tom lane >> >> > Björn, > > I think, the timing difference you see between 2 queries is caused by > delivering to the front-end (PgAdmin) and displaying all additional columns > that you include in the second query (much

Re: [PERFORM] Query with large number of joins

8GB RAM? In this case (if that's dedicated db server), you should try and set effective_cache_size around 40GB (not 4GB). Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] extremly bad select performance on huge table

ditional columns that you include in the second query (much bigger amount of data to pass from the db to the client). Pretty sure, if you do explain analyze on both queries, you'll see the same timing, because it'll reflect only db time without what's spent on delivering data to the client. Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] extremly bad select performance on huge table

an I > do to gain performance? Which parameters can I adapt? Having a huge > Linux machine with 72 GB RAM. > > Note: This select is just for testing. My final statement will be a > join on this table via the "mycolumn" column. > > Thanks for your help >

Re: [PERFORM] Partitions and work_mem?

From: Dave Johansen [mailto:davejohan...@gmail.com] Sent: Wednesday, October 15, 2014 4:49 PM To: Igor Neyman Cc: Josh Berkus; pgsql-performance Subject: Re: [PERFORM] Partitions and work_mem? On Wed, Oct 15, 2014 at 1:36 PM, Igor Neyman mailto:iney...@perceptron.com>> wrote: From

Re: [PERFORM] Partitions and work_mem?

From: Dave Johansen [mailto:davejohan...@gmail.com] Sent: Wednesday, October 15, 2014 4:20 PM To: Igor Neyman Cc: Josh Berkus; pgsql-performance Subject: Re: [PERFORM] Partitions and work_mem? On Wed, Oct 15, 2014 at 1:08 PM, Igor Neyman mailto:iney...@perceptron.com>> wrote: From:

Re: [PERFORM] Partitions and work_mem?

me to upgrade? RHEL 6 isn't EOLed and we're working on moving to RHEL 7 but it's a slow process that will probably take quite a bit of time, if it ever happens. Postgres 8.4 is EOL (RHEL). Igor Neyman

Re: [PERFORM] Seqscan on big table, when an Index-Usage should be possible

quot; > >Spalte | Typ | > >Attribute > > ++ > > --------++ > >

Re: [PERFORM] Possible performance regression in PostgreSQL 9.2/9.3?

gt; in postgresql.conf. If i were in your shoes, I'd be breaking the > > query down and figuring out where it goes off the rails. Best case > > scenario, you have a simplified, test case reproducible reduction of > > the problem that can help direct changes to the planner.

Re: [PERFORM] Issue with query scanning through all data even with indexes

ourceFilter" ON "SourceFilter"."communityId" = 1538 AND "SourceFilter"."groupId" = "NewsArticle"."groupId" AND(("SourceFilter"."id" IS NULL OR "SourceFilter"."id" = ANY("NewsArticle".&qu

Re: [PERFORM] Slow index scan on B-Tree index over timestamp field

From: Caio Casimiro [mailto:casimiro.lis...@gmail.com] Sent: Monday, November 04, 2013 4:33 PM To: Igor Neyman Cc: Jeff Janes; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Slow index scan on B-Tree index over timestamp field These are the parameters I have set in postgresql.conf

Re: [PERFORM] Slow index scan on B-Tree index over timestamp field

From: Caio Casimiro [mailto:casimiro.lis...@gmail.com] Sent: Monday, November 04, 2013 4:10 PM To: Igor Neyman Cc: Jeff Janes; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Slow index scan on B-Tree index over timestamp field Hi Neyman, thank you for your answer. Unfortunately this

Re: [PERFORM] Slow index scan on B-Tree index over timestamp field

eet_topic AS tt JOIN tweet AS t ON (tt.tweet_id = t.id AND t.creation_time BETWEEN 'D1' AND 'D2' AND t.user_id in (SELECT followed_id FROM relationship WHERE follower_id = N)) ORDER B

Re: [PERFORM] How clustering for scale out works in PostgreSQL

our 1000 users. What you need is PgBouncer, it should solv your problem. Please read some docs on PgBouncer, it's "light-weight" and very easy to setup. Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Efficient Correlated Update

_users". > > In looking it over, nothing jumped out at me as a problem.  Are you having > some problem with it, like poor performance or getting results different from > what you expected? > > -- > Kevin Grittner > EDB: http://www.enterprisedb.com > The Enterprise P

Re: [PERFORM] Efficiently query for the most recent record for a given user

, and do > > select * from call_activity where user_id = blarg order by called desc limit 1 > And most recent call for every user: SELECT id, user_id, MAX(called) OVER (PARTITION BY user_id) FROM call_activity; Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.

From: sl...@centrum.sk [mailto:sl...@centrum.sk] Sent: Wednesday, August 07, 2013 11:34 AM To: Igor Neyman; Pavel Stehule Cc: pgsql-performance@postgresql.org Subject: RE: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it. I got: "

Re: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.

From: sl...@centrum.sk [mailto:sl...@centrum.sk] Sent: Wednesday, August 07, 2013 10:43 AM To: Igor Neyman; Pavel Stehule Cc: pgsql-performance@postgresql.org Subject: RE: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it. You're righ

Re: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.

/cj2   Thank you.   Peter Slapansky - Your last explain analyze (with 3 settings set to 32) shows query duration 10ms, not 1sec. Am I wrong?  Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://ww

Re: [PERFORM] Partitions not Working as Expected

t will be re-planned properly, e.g.: lQueryString := 'SELECT MAX(cycle_date_time) AS MaxDT FROM gp_cycle_' || partition_extension::varchar || ' WHERE cell_id = ' || i_n_Cell_id::varchar || ' AND part_type_id = ' |

Re: [PERFORM] seqscan for 100 out of 3M rows, index present

From: Willy-Bas Loos [mailto:willy...@gmail.com] Sent: Wednesday, June 26, 2013 3:19 PM To: Igor Neyman Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] seqscan for 100 out of 3M rows, index present plan with enable_seqscan off: Aggregate  (cost=253892.48..253892.49 rows=1 width=0

Re: [PERFORM] seqscan for 100 out of 3M rows, index present

From: Willy-Bas Loos [mailto:willy...@gmail.com] Sent: Wednesday, June 26, 2013 3:04 PM To: Igor Neyman Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] seqscan for 100 out of 3M rows, index present nope $ grep ^[^#] /etc/postgresql/9.1/main/postgresql.conf|grep -e ^[^[:space

Re: [PERFORM] seqscan for 100 out of 3M rows, index present

s Loos -- So, did you try to set: enable_seqscan = off and see if different execution plan is more efficient? Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Slow SELECT by primary key? Postgres 9.1.2

> kind) are slow and one of the major challenges of database and hardware > engineering is working around their limitations. Fortunately it looks > like faster storage will soon be commonplace for reasonable prices. > > merlin > True. But, on the hand (back to original question)

Re: [PERFORM] Best practice when reindexing in production

From: Matheus de Oliveira [mailto:matioli.math...@gmail.com] Sent: Wednesday, May 29, 2013 10:19 AM To: Igor Neyman Cc: Niels Kristian Schjødt; Magnus Hagander; pgsql-performance@postgresql.org list Subject: Re: [PERFORM] Best practice when reindexing in production On Wed, May 29, 2013 at

Re: [PERFORM] Best practice when reindexing in production

.com.br/postgres I must be missing something here. But, how is that FK depends on the index? I understand FK lookup works much faster with the index supporting FK than without it, but you could have FK without index (on the "child" table). So, what gives? Regards, Igor Neyman -

Re: [PERFORM] Deterioration in performance when query executed in multi threads

From: Anne Rosset [aros...@collab.net] Sent: Monday, May 06, 2013 5:51 PM To: Igor Neyman; k...@rice.edu Cc: pgsql-performance@postgresql.org Subject: RE: [PERFORM] Deterioration in performance when query executed in multi threads Hi Igor, Result with

Re: [PERFORM] Deterioration in performance when query executed in multi threads

gt; > Also what are the values for > > cpu_tuple_cost > seq_page_cost > random_page_cost > effective_cache_size > > What kind of harddisk is in the server? SSD? Regular ones (spinning > disks)? > > > Also, with 8 CPUs, your max connection_pool size shouldn't much bigger than 20. Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Deterioration in performance when query executed in multi threads

> -Original Message- > From: Anne Rosset [mailto:aros...@collab.net] > Sent: Monday, May 06, 2013 1:01 PM > To: Igor Neyman; k...@rice.edu > Cc: pgsql-performance@postgresql.org > Subject: RE: [PERFORM] Deterioration in performance when query executed > in multi

Re: [PERFORM] Deterioration in performance when query executed in multi threads

> -Original Message- > From: Anne Rosset [mailto:aros...@collab.net] > Sent: Friday, May 03, 2013 4:52 PM > To: Igor Neyman; k...@rice.edu > Cc: pgsql-performance@postgresql.org > Subject: RE: [PERFORM] Deterioration in performance when query executed > in multi

Re: [PERFORM] [BUGS] BUG #8130: Hashjoin still gives issues

settings. > > > Stefan > I'd suggest that you adjust Postgres configuration, specifically memory settings (buffer_cache, work_mem, effective_cache_size), to reflect your hardware config, and see how it affects your query. Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Deterioration in performance when query executed in multi threads

you provide the results of: Explain analyze ; along with the definition of database objects (tables, indexes) involved in this select. Also, you mention client-side connection pooler. In my experience, server-side poolers, such as PgBouncer mentioned earlier, are much more effective. Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Re: Join between 2 tables always executes a sequential scan on the larger table

with optimizer parameters (lowering random_page_cost, lowering cpu_index_tuple_cost , increasing effective_cache_size, or just setting enable_seqscan = off), you could try to force "optimizer" to use index, and see if you are getting better results. Regards, Igor Neyman -- Sent

Re: [PERFORM] how to help the planner

ex_tuple_cost = 0.05 # same scale as above (default 0.005) cpu_operator_cost = 0.0075 # same scale as above (default 0.0025) Start with cpu_tuple_cost, increasing it from default 0.01 to 0.03-0.05. Regards, Igor Neyman From: Marty Frasier [mailto:m.fras...@escmatrix.

Re: [PERFORM] High CPU usage after partitioning

.34 rows=5 width=8) (actual time=0.021..0.021 rows=1 loops=1)" " Index Cond: (cycle_date_time IS NOT NULL)" " Heap Fetches: 0" May be you should upgrade to 9.2. Regards, Igor Neyman From: rudi [mailto:rudol...@

Re: [PERFORM] How can i find out top high load sql queries in PostgreSQL.

d more flexible than pgFouine. > http://dalibo.github.com/pgbadger/ > > Thanks & Regards, > Vibhor Kumar > EnterpriseDB Corporation > The Enterprise PostgreSQL Company > Blog:http://vibhork.blogspot.com > Pg_stat_statements extension tracks SQL statements execution stat

Re: [PERFORM] how to avoid deadlock on masive update with multiples delete

to block page and the try to scale to lock table while the delete process as some locked rows. Thanks! This (lock escalation from row -> to page -> to table) is MS SQL Server "feature", pretty sure Postgres does not do it. Regards, Igor Neyman -- Sent via pgsql-performa

Re: [PERFORM] Postgres becoming slow, only full vacuum fixes it

t be that problematic after all (but will take > longer due to the required dump/reload) > Actually, 8.3 to 8.4 required db dump/restore. When upgrading from 8.4 to 9.x pg_upgrade could be used without dump/restore. Regards, Igor Neyman -- Sent via pgsql-performance mailing l

Re: [PERFORM] Index with all necessary columns - Postgres vs MSSQL

7;::timestamp without time zone) AND ("timestamp" <= '2011-10-19 16:00:00'::timestamp without time zone))" reference different table and index names. Also, EXPLAIN ANALYZE would provide additional info compared to just EXPLAIN. One option you could try, is to cluster your table based on " test_all" index, and see if it makes a difference. BTW., in SQL Server your "covering" index - is it clustered? Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?

> -Original Message- > From: Craig Ringer [mailto:ring...@ringerc.id.au] > Sent: Thursday, November 03, 2011 5:07 AM > To: Igor Neyman > Cc: Robert Haas; Tom Lane; Jay Levitt; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Guide to PG's capabilities fo

Re: [PERFORM] Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?

> -Original Message- > From: Robert Haas [mailto:robertmh...@gmail.com] > Sent: Wednesday, November 02, 2011 11:13 AM > To: Tom Lane > Cc: Jay Levitt; pgsql-performance@postgresql.org > Subject: Re: Guide to PG's capabilities for inlining, predicate > hoisting, flattening, etc? > ...

Re: [PERFORM] Migrated from 8.3 to 9.0 - need to update config (re-post)

tart with the pg_bouncer: very simple to setup, reliable, no "extra" functionality, which seems by your message you don't need. Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] How to see memory usage using explain analyze ?

> -Original Message- > From: hyelluas [mailto:helen_yell...@mcafee.com] > Sent: Monday, August 15, 2011 2:33 PM > To: pgsql-performance@postgresql.org > Subject: Re: How to see memory usage using explain analyze ? > > Igor, > > thank you , my tests showed better performance against the l

Re: [PERFORM] How to see memory usage using explain analyze ?

query? > > thank you. > > Helen > > > > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/How-to-see-memory-usage-using- > explain-analyze-tp4694681p4694681.html > Sent from the PostgreSQL - performance mailin

Re: [PERFORM] Postgres 9.0 has a bias against indexes

> -Original Message- > From: Scott Marlowe [mailto:scott.marl...@gmail.com] > Sent: Thursday, January 27, 2011 4:25 PM > To: Igor Neyman > Cc: Mladen Gogala; Tom Lane; David Wilson; Kenneth Marshall; > pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Po

Re: [PERFORM] Postgres 9.0 has a bias against indexes

> -Original Message- > From: Scott Marlowe [mailto:scott.marl...@gmail.com] > Sent: Thursday, January 27, 2011 4:16 PM > To: Igor Neyman > Cc: Mladen Gogala; Tom Lane; David Wilson; Kenneth Marshall; > pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Po

Re: [PERFORM] Postgres 9.0 has a bias against indexes

> -Original Message- > From: Scott Marlowe [mailto:scott.marl...@gmail.com] > Sent: Thursday, January 27, 2011 3:59 PM > To: Mladen Gogala > Cc: Igor Neyman; Tom Lane; David Wilson; Kenneth Marshall; > pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Po

Re: [PERFORM] Postgres 9.0 has a bias against indexes

this is exclusive Postgres feature. I'm pretty sure, Oracle optimizer will do "TABLE ACCESS (FULL)" instead of using index on 14-row table either. Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Real vs Int performance

e > To see what happens with parametrized query in "real life" you could try "auto_explain" contrib module. Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning question 1

> -Original Message- > From: Ben [mailto:midfi...@gmail.com] > Sent: Friday, October 29, 2010 12:16 PM > To: Igor Neyman > Cc: pgsql-performance@postgresql.org > Subject: Re: partitioning question 1 > > On Oct 29, 2010, at 7:38 AM, Igor Neyman wrote: > >

Re: [PERFORM] partitioning question 1

will be more efficient than full table scan of non-partitioned table. So, yes partitioning provides performance improvements, not only maintenance convenience. Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Index scan is not working, why??

ieving substancial portion of big table seq scan is usually faster, that's why optimizer chooses it. Your queries (and possibly data sets in the tables on different servers) are not the same. Your first query (which uses seq scan) returns 259671 which is probably substantial part of the whole table. Your second query (which uses index scan) returns only 4808 rows, which makes index access less costly in this case. Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] oracle to psql migration - slow query in postgres

> >4 - filter("EMAILBOUNCED"=0 AND "EMAILOK"=1) > > 16 rows selected. > > 1. Postgres doesn't have "FAST FULL SCAN" because even if all the info is in the index, it need to visit the row in the table (&quo

Re: [PERFORM] oracle to psql migration - slow query in postgres

rs > seq_scan | 298 > seq_tup_read | 42791828896 > idx_scan | 31396925 > idx_tup_fetch| 1083796963 > n_tup_ins| 291308316 > n_tup_upd | 0 > n_tup_del| 4188020 > n_tup_hot_upd| 0 > n_live_tup | 285364632 > n_dead

Re: [PERFORM] Identical query slower on 8.4 vs 8.3

got similar runtime on both 8.4.4 and 8.3.7, here is a suggestion to improve performance of this query based on EXPLAIN ANALYZE you proveded (should have done it in your first e-mail). EXPLAIN ANALYZE shows that most of the time (22015 ms on 8.4.4) spent on sorting you result set. And according to

Re: [PERFORM] Internal operations when the planner makes a hash join.

le has 100 entries or 1 entries. > The drawback is that the whole thing has to fit in RAM. > > > > > > > >> -- > >> Alvaro Herrera > http://www.CommandPrompt.com/ > >> The PostgreSQL Company - Command P