Re: [PERFORM] Querying with multicolumn index

ing denormalizing the data and keeping a "cache" of the same data in another table. pgsql version: 9.3.14 and 9.5.3, already tried vacuum analyze. Thanks, Eric   You should be having this index:   create index updates_driver_time_idx ON updates(driver_id, "time" DES

Re: [PERFORM] Tuning one Recurcive CTE

quadrant.com/postgresql-ctes-are-optimization-fences/ https://robots.thoughtbot.com/advanced-postgres-performance-tips#common-table-expressions-and-subqueries https://www.postgresql.org/message-id/CAPo4y_XUJR1sijvTySy9W%2BShpORwzbhSdEzE9pgtc1%3DcTkvpkw%40mail.gmail.com   -- Andreas Joseph Krogh CTO / Part

Re: [PERFORM] Tuning one Recurcive CTE

one; Always provide the query in question when asking for help tuning it.   -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>  

Re: [PERFORM] Very poor performance with Nested Loop Anti Join

På tirsdag 02. august 2016 kl. 01:15:05, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: Andreas Joseph Krogh writes: > This query performs terribly slow (~26 minutes,��1561346.597ms): Seems like the key misestimation is on the inner antijoin: >                ->  Hash Anti Joi

Re: [PERFORM] Very poor performance with Nested Loop Anti Join

På mandag 01. august 2016 kl. 15:33:04, skrev Andreas Joseph Krogh < andr...@visena.com <mailto:andr...@visena.com>>: I have this schema:   CREATE TABLE onp_crm_person( id serial PRIMARY KEY, onp_user_id bigint referencesonp_user(id) deferrable initially deferred, is_resource boole

[PERFORM] Very poor performance with Nested Loop Anti Join

ICE_STATUS_INVOICED'::text)) Rows Removed by Filter: 151 Planning time: 3.510 ms Execution time: 338.349 ms (39 rows) So my question is is there something I can do to make PG favor a Hash Anti Join instead of a Nested Loop An

Re: [PERFORM] Searching GIN-index (FTS) and sort by timestamp-column

På mandag 21. mars 2016 kl. 16:13:07, skrev Oleg Bartunov mailto:obartu...@gmail.com>>:     On Mon, Mar 21, 2016 at 5:41 PM, Andreas Joseph Krogh mailto:andr...@visena.com>> wrote: På lørdag 19. mars 2016 kl. 03:44:55, skrev Jeff Janes mailto:jeff.ja...@gmail.com>>: On Wed, Ma

Re: [PERFORM] Searching GIN-index (FTS) and sort by timestamp-column

På lørdag 19. mars 2016 kl. 03:44:55, skrev Jeff Janes mailto:jeff.ja...@gmail.com>>: On Wed, Mar 16, 2016 at 6:53 AM, Andreas Joseph Krogh mailto:andr...@visena.com>> wrote: På onsdag 16. mars 2016 kl. 14:37:27, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: Andreas Joseph

Re: [PERFORM] Searching GIN-index (FTS) and sort by timestamp-column

På onsdag 16. mars 2016 kl. 14:37:27, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: Andreas Joseph Krogh writes: > 1. Why isnt' folder_id part of the index-cond? Because a GIN index is useless for sorting. > 2. Is there a way to make it use the (same) index to sort by >

Re: [PERFORM] Searching GIN-index (FTS) and sort by timestamp-column

På onsdag 16. mars 2016 kl. 16:04:08, skrev Evgeniy Shishkin < itparan...@gmail.com <mailto:itparan...@gmail.com>>: > On 16 Mar 2016, at 17:52, Evgeniy Shishkin wrote: > > >> On 16 Mar 2016, at 16:37, Tom Lane wrote: >> >> Andreas Joseph Krogh writ

Re: [PERFORM] Searching GIN-index (FTS) and sort by timestamp-column

;>> On 16 Mar 2016, at 16:37, Tom Lane wrote: >>> >>> Andreas Joseph Krogh writes: >>>> 1. Why isnt' folder_id part of the index-cond? >>> >>> Because a GIN index is useless for sorting. >> >> I don't see how gin inabi

Re: [PERFORM] Searching GIN-index (FTS) and sort by timestamp-column

På onsdag 16. mars 2016 kl. 14:53:04, skrev Andreas Joseph Krogh < andr...@visena.com <mailto:andr...@visena.com>>: På onsdag 16. mars 2016 kl. 14:37:27, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: Andreas Joseph Krogh writes: > 1. Why isnt' folder_id part of the i

Re: [PERFORM] Searching GIN-index (FTS) and sort by timestamp-column

type?   Thanks.   -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>  

[PERFORM] Searching GIN-index (FTS) and sort by timestamp-column

th=0) (actual time=134.903..134.903 rows=282513 loops=1) Index Cond: (fts_all @@ '''andre'':*'::tsquery)  Planning time: 0.530 ms  Execution time: 347.967 ms (13 rows)     1. Why isnt' folder_id part of the index-cond? 2. Is there a way t

Re: [PERFORM] problem with select *

Kindly tell me where it is going wrong. I have 6MB cache, 1.6GHz CPU, linux 14.04 OS, 8GB RAM.   In what application are you performing these queries?   -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>  

Re: [PERFORM] Slow HashAggregate/cache access

 Buffers: shared hit=2          ->  Seq Scan on fr13t3  (cost=0.00..4.30 rows=184 width=17) (actual time=0.003..0.027 rows=184 loops=1)                Filter: (fr01codemp = 1::smallint)                Buffers: shared hit=2  Total runtime: 18.528 ms (35 rows)     Tomorrow I will try to do the same with the ot

Re: [PERFORM] Slow HashAggregate/cache access

and comment.   -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>  

Re: [PERFORM] Slow HashAggregate/cache access

ll 9.5 help here as it has "WHERE clause pushdown in subqueries with window functions"? http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-highlight-where-pushdown-with-window-function/   Are you able to try 9.5 and post the results?   Thanks.   -- Andreas Joseph Krogh CTO /

Re: [PERFORM] New server: SSD/RAID recommendations?

ss you have many BLOBs in pg_largeobject, then move that to a separate tablespace with "archive-grade"-disks (spinning disks).   -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>  

[PERFORM] Performance of vacuumlo

s in RAID5.   Any hints on how long this is supposed to take?   Thanks.   -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>

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

På mandag 09. februar 2015 kl. 22:36:55, skrev Igor Neyman < iney...@perceptron.com <mailto:iney...@perceptron.com>>:     From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org]On Behalf Of Andreas Joseph Krogh Sent: Monday, February 09, 201

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

y ways around this or do people simply avoid having FKs in schemas like this?   Thanks.   -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>

[PERFORM] Query performing very bad and sometimes good

0.00..4.75 rows=1 width=4) (actual time=0.023..0.023 rows=0 loops=75) Filter: ((favourite_for = 3::bigint) AND (favourite_item = comp.entity_id))      Rows Removed by Filter: 183  Total runtime: 3481.136 ms (42 rows)   Does anyone see anything obvious or have

Re: [PERFORM] OFFSET/LIMIT - Disparate Performance w/ Go application

På torsdag 12. juni 2014 kl. 16:58:06, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: Matt Silverlock writes: > Hi all. This might be tricky in so much as there���s a few moving parts (when isn���t there?), but I���ve tried to test the postgres side as much as possible. > Trying to work out a pot

Re: [PERFORM] Optimize query for listing un-read messages

På fredag 02. mai 2014 kl. 02:17:58, skrev Craig James mailto:cja...@emolecules.com>>: On Thu, May 1, 2014 at 4:26 AM, Andreas Joseph Kroghmailto:andr...@visena.com>> wrote: I have a schema where I have lots of messages and some users who might have read some of them. When a message is read by a

Re: [PERFORM] Optimize query for listing un-read messages

På fredag 02. mai 2014 kl. 01:58:04, skrev David G Johnston < david.g.johns...@gmail.com >: Per-User caching does seem to be something that is going to be needed... Depending on how many users are being tracked would storing the "reader_id" in an indexed arra

Re: [PERFORM] Optimize query for listing un-read messages

På fredag 02. mai 2014 kl. 00:55:25, skrev David G Johnston < david.g.johns...@gmail.com <mailto:david.g.johns...@gmail.com>>: Andreas Joseph Krogh-2 wrote > I will end up with that only if > all users read all messages, which is not nearly the case. These observations proba

Re: [PERFORM] Optimize query for listing un-read messages

På fredag 02. mai 2014 kl. 00:34:34, skrev Tomas Vondra mailto:t...@fuzzy.cz>>: On 1.5.2014 23:58, Andreas Joseph Krogh wrote: > På torsdag 01. mai 2014 kl. 23:45:49, skrev Tomas Vondra <mailto:t...@fuzzy.cz>>: > >     On 1.5.2014 23:19, Andreas Joseph Krogh wrot

Re: [PERFORM] Optimize query for listing un-read messages

På torsdag 01. mai 2014 kl. 23:45:49, skrev Tomas Vondra mailto:t...@fuzzy.cz>>: On 1.5.2014 23:19, Andreas Joseph Krogh wrote: > På torsdag 01. mai 2014 kl. 23:02:13, skrev Pavel Stehule > mailto:pavel.steh...@gmail.com>>: > >      >      >     2014-05-01

Re: [PERFORM] Optimize query for listing un-read messages

På torsdag 01. mai 2014 kl. 23:19:55, skrev David G Johnston < david.g.johns...@gmail.com >: How does something like: WITH unreads AS ( SELECT messageid FROM message EXCEPT SELECT messageid FROM message_property WHERE personid=1 AND has_read ) SELECT ...

Re: [PERFORM] Optimize query for listing un-read messages

På torsdag 01. mai 2014 kl. 23:02:13, skrev Pavel Stehule < pavel.steh...@gmail.com >:     2014-05-01 22:30 GMT+02:00 Andreas Joseph Kroghmailto:andr...@visena.com>>: På torsdag 01. mai 2014 kl. 21:53:32, skrev Pavel Stehule < pavel.steh...@gmail.com

Re: [PERFORM] Optimize query for listing un-read messages

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 Kroghmailto:andr...@visena.com>>: På torsdag 01. mai 2014 kl. 21:30:39, skrev Pavel Stehule < pavel.steh...@gmail.com

Re: [PERFORM] Optimize query for listing un-read messages

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.postgresql.org/docs/9.3/interactive/sql-createindex.html

Re: [PERFORM] Optimize query for listing un-read messages

På torsdag 01. mai 2014 kl. 20:35:07, skrev Jochem Berndsen mailto: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 person_id, FALSE AS is_read, m.subject FROM message m WHERE 1 = 1

[PERFORM] Optimize query for listing un-read messages

Hi all,   I'm using PostgreSQL 9.3.2 on x86_64-unknown-linux-gnu I have a schema where I have lots of messages and some users who might have read some of them. When a message is read by a user I create an entry i a table message_property holding the property (is_read) for that user.   The schem