Re: [PERFORM] Hi

On Thu, Aug 24, 2017 at 11:49 PM, Daulat Ram wrote: > Hello, > > > > Would I request to help me on this query. > > > > SELECT 'Inspection Completed' as "ALL Status" ,COUNT(*) as "Number of > Count" FROM ud_document WHERE status = 'Inspection Completed' union SELECT > 'Pending', COUNT(*) FROM ud_d

Re: [PERFORM] Unlogged tables

On Wed, Aug 9, 2017 at 3:39 AM, Michael Paquier wrote: > This triggers a table rewrite and makes sure that all the data gets > WAL-logged. The cost to pay for durability. > > > Is there a way to get my cake and eat it too? > > Not completely. Making data durable will have a cost at the end, but >

Re: [PERFORM] join estimate of subqueries with range conditions and constraint exclusion

On Wed, May 24, 2017 at 2:17 PM, Justin Pryzby wrote: > We got bitten again by what appears to be the same issue I reported > (perhaps > poorly) here: > https://www.postgresql.org/message-id/20170326193344. > GS31628%40telsasoft.com > > We have PG9.6.3 table heirarchies partitioned by time. Our

Re: [PERFORM] Can postgresql plan a query using multiple CPU cores?

On Monday, May 22, 2017, Clemens Eisserer wrote: > 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

Re: [PERFORM] Create a materialized view in PostgreSQL which can be access by all the user account

On Mon, Apr 17, 2017 at 10:00 AM, Dinesh Chandra 12108 < dinesh.chan...@cyient.com> wrote: > Hi Experts, > > > > How can we create a materialized view in PostgreSQL which can be access by > all the user account in all Database? > ​Databases are isolated - while connected to one you cannot directl

Re: [PERFORM] Filter certain range of IP address.

On Fri, Apr 7, 2017 at 8:18 AM, Dinesh Chandra 12108 < dinesh.chan...@cyient.com> wrote: > Dear Vinny, > > Thanks for your valuable replay. > > but I need a select query, which select only that record which starts from > IP "172.23.110" only from below table. > > xxx 172.23.110.175 > yyy 1

Re: [PERFORM] anti-join with small table via text/varchar cannot estimate rows correctly

On Wed, Mar 1, 2017 at 5:24 PM, Jeff Janes wrote: > On Wed, Mar 1, 2017 at 2:12 PM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Wed, Mar 1, 2017 at 3:00 PM, Stefan Andreatta > > wrote: >> >>> plain analyze >>> select t

Re: [PERFORM] anti-join with small table via text/varchar cannot estimate rows correctly

On Wed, Mar 1, 2017 at 3:00 PM, Stefan Andreatta wrote: > plain analyze > select tmp_san_1.id > from tmp_san_1 >left join tmp_san_2 on tmp_san_1.text = tmp_san_2.text > where tmp_san_2.id is null; > > ​Does it help if you check for "tmp_san_2.text is null"? David J.

Re: [PERFORM] Number of characters in column preventing index usage

On Fri, Feb 17, 2017 at 3:49 PM, Tomas Vondra wrote: > That may seem a bit strange, but I'd bet it finds the short value in some > statistic (MCV, histogram) ans so can provide very accurate estimate. ​​ -> Index Only Scan using tab_idx1 on tab (cost=0.27..8.29 rows=1 width=0) (actual time=0

Re: [PERFORM] Number of characters in column preventing index usage

On Fri, Feb 17, 2017 at 3:19 PM, Hustler DBA wrote: > > my_db=# create index tab_idx1 on tab(ID); > > CREATE INDEX > my_db=# explain (analyze, buffers) select count(*) from tab where ID = ' > 01625cfa-2bf8-45cf' ; > QUERY PLAN > >

Re: [PERFORM] optimizing immutable vs. stable function calls?

On Wed, Jan 18, 2017 at 4:23 PM, Tom Lane wrote: > "David G. Johnston" writes: > > ​I'm feeling a bit dense here but even after having read a number of > these > > kinds of interchanges I still can't get it to stick. I think part of the > > probl

Re: [PERFORM] optimizing immutable vs. stable function calls?

On Wed, Jan 18, 2017 at 3:54 PM, Tom Lane wrote: > Karl Czajkowski writes: > > The query planner does not seem to > > recognize that it can eliminate redundant calls to a STABLE function. > > No, it doesn't. > > > In my case, the function call does not take any arguments and is thus > > triviall

Re: [PERFORM] why we do not create indexes on master

On Tue, Dec 27, 2016 at 10:38 AM, Valerii Valeev wrote: > Thank you David, > > I used same rationale to convince my colleague — it didn’t work :) > Sort of “pragmatic” person who does what seems working no matter what > happens tomorrow. > So I’m seeking for better understanding of what's happeni

Re: [PERFORM] why we do not create indexes on master

On Tue, Dec 27, 2016 at 8:22 AM, Valerii Valeev wrote: > I have naive idea that it won’t help if index is created before the data > is there — i.e. indexes on master aren’t updated when data loaded to child > table. > ​Indexes on the master table of a partition scheme never reflect the contents

Re: [PERFORM] materialized view order by and clustering

On Thu, Nov 17, 2016 at 9:36 AM, Rick Otten wrote: > > Does it matter if I also try to CLUSTER the materialized view on that > primary sort field? Or is it already clustered because of the 'order by'? > > ​[...]​ > > When I refresh the materialized view (concurrently) is the order_by > preserved?

Re: [PERFORM] [HACKERS] temporary table vs array performance

Its considered bad form to post to multiple lists. Please pick the most relevant one - in this case I'd suggest -general. On Mon, Sep 26, 2016 at 8:39 AM, dby...@163.com wrote: > > Array is not convenient to use in function, whether > there are other methods can be replaced temp table in functi

Re: [PERFORM] Performance problems with 9.2.15

On Thu, Jul 21, 2016 at 2:24 PM, Claudio Freire wrote: > That cross join doesn't look right. It has no join condition. ​That is that the definition of a "CROSS JOIN"... David J.

Re: [PERFORM] Index not used

On Thu, Jun 16, 2016 at 11:05 AM, Tom Lane wrote: > meike.talb...@women-at-work.org writes: > > When I query this through pgsql, the queries are fast as expected. > > select * from push_topic where guid = > 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5' > > Index Scan using push_topic_idx_topicguid on pu

Re: [PERFORM] pg_restore seems very slow

On Wed, Jun 15, 2016 at 6:00 PM, Adrian Myers wrote: > This is my first post to the mailing list, so I apologize for any > etiquette issues. > > I have a few databases that I am trying to move from one system to > another. Both systems are running Windows 7 and Postgres 8.4, and they are > prett

Re: [PERFORM] Performance of LIKE/NOT LIKE when used in single query

On Wednesday, June 8, 2016, Ed Felstein wrote: > Hello, > First time poster here. Bear with me. > Using PostgreSQL 9.5 > I have a situation where I have a LIKE and a NOT LIKE in the same query to > identify strings in a varchar field. Since I am using wildcards, I have > created a GIN index on

Re: [PERFORM] array size exceeds the maximum allowed (1073741823) when building a json

On Tue, Jun 7, 2016 at 8:42 AM, Nicolas Paris wrote: > > > 2016-06-07 14:39 GMT+02:00 David G. Johnston : > >> On Tue, Jun 7, 2016 at 8:36 AM, Nicolas Paris >> wrote: >> >>> 2016-06-07 14:31 GMT+02:00 David G. Johnston >> >: >>> >&

Re: [PERFORM] array size exceeds the maximum allowed (1073741823) when building a json

On Tue, Jun 7, 2016 at 8:36 AM, Nicolas Paris wrote: > 2016-06-07 14:31 GMT+02:00 David G. Johnston : > >> On Tue, Jun 7, 2016 at 7:44 AM, Nicolas Paris >> wrote: >> >>> Hello, >>> >>> I run a query transforming huge tables to a json document

Re: [PERFORM] array size exceeds the maximum allowed (1073741823) when building a json

On Tue, Jun 7, 2016 at 7:44 AM, Nicolas Paris wrote: > Hello, > > I run a query transforming huge tables to a json document based on a period. > It works great for a modest period (little dataset). > However, when increasing the period (huge dataset) I get this error: > > SQL ERROR[54000] > ERROR

Re: [PERFORM] similarity and operator '%'

On Mon, May 30, 2016 at 1:53 PM, Volker Boehm wrote: > > The reason for using the similarity function in place of the '%'-operator > is that I want to use different similarity values in one query: > > select name, street, zip, city > from addresses > where name % $1 > and stre

Re: [PERFORM] Odd behavior with indices

On Fri, Feb 26, 2016 at 1:38 PM, joe meiring wrote: > Here's the distribution of parameter_id's > > select count(parameter_id), parameter_id from datavalue group by parameter_id > 88169 142889171 815805 178570124257262 213947049 151225902 24091090 > 3103877 10633764 11994442 1849232

Re: [PERFORM] Odd behavior with indices

On Fri, Feb 26, 2016 at 12:43 PM, joe meiring wrote: > Also available on S.O.: > > > http://stackoverflow.com/questions/35658238/postgres-odd-behavior-with-indices > > I've got a datavalue table with ~200M rows or so, with indices on both > site_id and parameter_id. I need to execute queries like

Re: [PERFORM] Getting an optimal plan on the first execution of a pl/pgsql function

On Mon, Dec 14, 2015 at 11:53 AM, Pedro França wrote: > I have a really busy function that I need to optimize the best way I can. > This function is just a nested select statement that is requested several > times a sec by a legacy application. I'm running a PostgreSQL 9.4 on a > CentOS 6; > > Th

Re: [PERFORM] No index only scan on md5 index

On Wednesday, November 25, 2015, Adam Brusselback wrote: > Hey all, > > I have an attachment table in my database which stores a file in a bytea > column, the file name, and the size of the file. > > Schema: > CREATE TABLE attachment > ( > attachment_id uuid NOT NULL DEFAULT gen_random_uuid(),

Re: [PERFORM] Partition Constraint Exclusion Limits

On Tue, Oct 27, 2015 at 2:29 PM, GMail wrote: > I have partitioned a large table in my PG database (6.7 billion rows!) by > a date column and in general constraint exclusion works well but only in > relatively simple case when the partition key is specified exactly as > created in the CHECK const

[PERFORM] Re: [PERFORM] Re: Query > 1000× slowdown after adding datetime comparison

On Mon, Aug 31, 2015 at 3:19 PM, twoflower wrote: > And another thing which comes out as a little surprising to me - if I > replace > the *date_last_updated* condition with another one, say *doc.documenttype = > 4*, the query finishes immediately. *documenttype* is an unindexed integer > column.

[PERFORM] Re: [PERFORM] Re: Query > 1000× slowdown after adding datetime comparison

On Mon, Aug 31, 2015 at 3:03 PM, twoflower wrote: > Tomas Vondra-4 wrote > > Please share explain plans for both the slow and the fast query. That > > makes it easier to spot the difference, and possibly identify the cause. > > > > Also, what PostgreSQL version is this, and what are "basic" confi

[PERFORM] Re: [PERFORM] Query > 1000× slowdown after adding datetime comparison

On Mon, Aug 31, 2015 at 12:09 PM, twoflower wrote: > I have the following three tables: > > DOCUMENT > id (index) > documenttype > date_last_updated: timestamp(6) (indexed) > > EXTERNAL_TRANSLATION_UNIT > id (indexed) > fk_id_document (indexed) > > EXTERNAL_TRANSLATION > id (indexed)

Re: [PERFORM] Most efficient way of querying M 'related' tables where N out of M may contain the key

On Fri, Aug 21, 2015 at 8:07 AM, Stephane Bailliez wrote: > > On Thu, Aug 20, 2015 at 8:19 PM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> >> ​SELECT [...] >> FROM (SELECT reference_id, [...] FROM table_where_referenced_id_is_a_pk

Re: [PERFORM] Most efficient way of querying M 'related' tables where N out of M may contain the key

On Thu, Aug 20, 2015 at 8:03 PM, Stephane Bailliez wrote: > Pretty bad subject description... but let me try to explain. > > > I'm trying to figure out what would be the most efficient way to query > data from multiple tables using a foreign key. > > ​SELECT [...] FROM (SELECT reference_id, [...]

Re: [PERFORM] Insert vs Update

On Wed, Jul 15, 2015 at 4:53 PM, Michael Nolan wrote: > On Wed, Jul 15, 2015 at 3:16 PM, Robert DiFalco > wrote: > >> >> Thanks David, my example was a big simplification, but I appreciate your >> guidance. The different event types have differing amounts of related data. >> Query speed on this

Re: [PERFORM] Insert vs Update

On Wed, Jul 15, 2015 at 3:16 PM, Robert DiFalco wrote: > The different event types have differing amounts of related data. > ​On this basis alone I would select the multiple-table version as my baseline and only consider something different if the performance of this was insufficient and I could

Re: [PERFORM] Insert vs Update

On Wed, Jul 15, 2015 at 1:56 PM, Robert DiFalco wrote: > > > On Wed, Jul 15, 2015 at 10:33 AM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Wednesday, July 15, 2015, Robert DiFalco >> wrote: >> >>> First off I apologize if

Re: [PERFORM] Insert vs Update

On Wednesday, July 15, 2015, Robert DiFalco wrote: > First off I apologize if this is question has been beaten to death. I've > looked around for a simple answer and could not find one. > > Given a database that will not have it's PKEY or indices modified, is it > generally faster to INSERT or UP

Re: [PERFORM] Insert vs Update

On Wed, Jul 15, 2015 at 12:16 PM, Robert DiFalco wrote: > First off I apologize if this is question has been beaten to death. I've > looked around for a simple answer and could not find one. > > Given a database that will not have it's PKEY or indices modified, is it > generally faster to INSERT

Re: [PERFORM] Are there tuning parameters that don't take effect immediately?

On Fri, Jun 12, 2015 at 4:37 PM, Michael Nolan wrote: > The only thing I can come up that's happened since last night was that we > ran the nightly vacuum analyze on that database, but I did not change the > statistics target. > ​The answer to your question is no, parameters changes are worse w

Re: [PERFORM] How to reduce writing on disk ? (90 gb on pgsql_tmp)

You should repost this directly and not through Nabble. It has wrapped your code in raw tags which the PostgreSQL mailing list software strips. On Wednesday, June 3, 2015, ben.play wrote: > Hi all, > > We have a big database (more than 300 Gb) and we run a lot of queries each > minute. > > Howe

Re: [PERFORM] Fastest way / best practice to calculate "next birthdays"

On Thursday, May 21, 2015, Bosco Rama wrote: > On 05/20/15 20:22, David G. Johnston wrote: > > On Monday, May 18, 2015, er.tejaspate...@gmail.com < > > er.tejaspate...@gmail.com > wrote: > > > >> If I have to find upcoming birthdays in current week a

Re: [PERFORM] Fastest way / best practice to calculate "next birthdays"

On Monday, May 18, 2015, er.tejaspate...@gmail.com < er.tejaspate...@gmail.com> wrote: > If I have to find upcoming birthdays in current week and the current week > fall into different months - how would you handle that? > Extract(week from timestamptz_column) ISO weeks are not affected by month

Re: [PERFORM] unlogged tables

On Mon, Apr 13, 2015 at 7:45 PM, Jim Nasby wrote: > On 4/13/15 7:32 PM, David G. Johnston wrote:​ > > That particular use-case would probably best be served with a separate >> replication channel which pushes data files from the primary to the >> slaves and allows for t

Re: [PERFORM] unlogged tables

On Mon, Apr 13, 2015 at 4:49 PM, Jeff Janes wrote: > On Mon, Apr 13, 2015 at 1:49 PM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Monday, April 13, 2015, Matheus de Oliveira >> wrote: >> >>> >>> On Mon, Apr 13, 2015 at 4:31

Re: [PERFORM] unlogged tables

On Monday, April 13, 2015, Matheus de Oliveira wrote: > > On Mon, Apr 13, 2015 at 4:31 PM, dgabriel > wrote: > >> "In the event of a normal shutdown, we can flush all the writes to disk >> so we know all the data has been written, so there is no need to >> truncate." >> >> Isn't possible to peri

Re: [PERFORM] Weird CASE WHEN behaviour causing query to be suddenly very slow

On Tue, Mar 31, 2015 at 8:58 AM, Kevin Viraud < kevin.vir...@rocket-internet.de> wrote: > Touche ! Thanks a lot. > > Looking more at the data yes it goes very often to ELSE Clause. And > therefore reaching the MAX_CACHED_RES. > > In there anyway to increase that value ? > > Basically, I have se

Re: [PERFORM] query - laziness of lateral join with function

Tom Lane-2 wrote > paulcc < > paulcc.two@ > > writes: >>select count(alpha.id) >>from alpha >>cross join lateral some_function(alpha.id) as some_val >>where alpha.test > >>Here the function is strict, and moreover its argument will never >>be null - hence there should al

[PERFORM] Re: Migrating a FoxPro system and would like input on the best way to achieve optimal performance

TonyS wrote > Then each client has files within their own directory to keep the size of > the tables manageable. Each client has 165 tables. These tables are all > the same definition across the different groups. > > I have considered partitioning tables, but if I am correct that would > result in

Re: [PERFORM] Postgres does not use indexes with OR-conditions

Kevin Grittner-5 wrote > Andrew Dunstan < > andrew@ > > wrote: >> On 11/07/2014 12:06 AM, Vlad Arkhipov wrote: > >>> I need to rewrite it in the way below to make Postgres use the index. >>> >>> select * >>> from commons.financial_documents fd >>> where fd.creation_time <= '2011-11-07 10:39:07.2

Re: [PERFORM] IS NOT NULL and LEFT JOIN

David G Johnston wrote > > Laurent Martelli wrote >> Le 20/10/2014 15:58, Tom Lane a écrit : >>> Laurent Martelli < >> laurent.martelli@ >> > writes: >>>> Do we agree that both queries are identical ? >>> No, they *aren't* id

Re: [PERFORM] IS NOT NULL and LEFT JOIN

Laurent Martelli wrote > Le 20/10/2014 15:58, Tom Lane a écrit : >> Laurent Martelli < > laurent.martelli@ > > writes: >>> Do we agree that both queries are identical ? >> No, they *aren't* identical. Go consult any SQL reference. Left join >> conditions don't work the way you seem to be thinki

Re: [PERFORM] Slow query

Ross Elliott-2 wrote > Maybe someone can explain this. The following SQL will reproduce our > issue: > DROP TABLE IF EXISTS t1 CASCADE; > CREATE TABLE t1 (name text, > state text); > CREATE INDEX t1_name ON t1(name); > CREATE INDEX t1_state ON t1(state); > CREATE INDEX t1_name_stat

Re: [PERFORM] weird execution plan

Huang, Suya wrote > Both queries have been run several times so cache would have same effect > on both of them? Below is the plan with buffer information. Not everyone does so its nice to make certain - especially since I'm not all that familiar with the code involved. But since no one else has

Re: [PERFORM] weird execution plan

Huang, Suya wrote > Can someone figure out why the first query runs so slow comparing to the > second one? They generate the same result... Try: EXPLAIN (ANALYZE, BUFFERS) I believe you are only seeing caching effects. David J. -- View this message in context: http://postgresql.1045698.n5.n

Re: [PERFORM] query performance with hstore vs. non-hstore

Huang, Suya wrote > See output of explain (analyze,timing off), the total runtime is close to > the one enable timing. Calling 43s "close to" 70s doesn't sound right... > dev=# explain (analyze, timing off) select cha_type, sum(visits) from > (select (each(visits)).key as cha_type,(each(visits))

Re: [PERFORM] autocommit (true/false) for more than 1 million records

On Tue, Aug 26, 2014 at 9:21 PM, Alex Goncharov-2 [via PostgreSQL] < ml-node+s1045698n5816426...@n5.nabble.com> wrote: > Thank you, Kevin -- this is helpful. > > But it still leaves questions for me. > > > Kevin Grittner <[hidden email] > > wro

Re: [PERFORM] autocommit (true/false) for more than 1 million records

Emi Lu-2 wrote > Hello, > > Trying to insert into one table with 1 million records through java JDBC > into psql8.3. May I know (1) or (2) is better please? > > (1) set autocommit(true) > (2) set autocommit(false) > commit every n records (e.g., 100, 500, 1000, etc) > > Thanks a lot! > Em

Re: [PERFORM] query on parent partition table has bad performance

Huang, Suya wrote > Hi, > > I have a question about partition table query performance in postgresql, > it's an old version 8.3.21, I know it's already out of support. so any > words about the reason for the behavior would be very much appreciated. > > I have a partition table which name is test_r

Re: [PERFORM] two table join with order by on both tables attributes

Evgeniy Shishkin wrote > Hello, > > suppose you have two very simple tables with fk dependency, by which we > join them > and another attribute for sorting > > like this > select * from users join notifications on users.id=notifications.user_id > ORDER BY users.priority desc ,notifications.prior

Re: [PERFORM] Query performing very bad and sometimes good

Andreas Joseph Krogh-2 wrote > Hi all.   Running version: on=> select version(); >   version > > >  PostgreSQL 9.3.2 on x86_64-unknown-lin

[PERFORM] Re: Slow query with indexed ORDER BY and LIMIT when using OR'd conditions

> BTW this is to my understanding a very similar scenario to how partitioned > tables work and push down limit and where conditions. Why is this not > possible in this case? > > Jano > > > On Mon, Jul 21, 2014 at 11:54 PM, David G Johnston < > david.g.johnston@ >

[PERFORM] Re: Slow query with indexed ORDER BY and LIMIT when using OR'd conditions

johno wrote > Thanks for the quick reply David! > > However I am still unsure how these two queries are not relationally > equivalent. I am struggling to find a counterexample where the first and > third query (in email, not in gist) would yield different results. Any > ideas? Remove the outer LI

[PERFORM] Re: Slow query with indexed ORDER BY and LIMIT when using OR'd conditions

johno wrote > The question is... why is the query planner unable to make this > optimization for the slow query? What am I missing? Short answer - your first and last queries are not relationally equivalent and the optimizer cannot change the behavior of the query which it is optimizing. i.e. you

Re: [PERFORM] UNION and bad performance

pinker wrote >>> rhaas=# explain select a from generate_series(1,100) a union select a >>> from generate_series(1,100) a; >>> QUERY PLAN >>> -- >>> HashAggregate (cost=45.00..65

Re: [PERFORM] parse/bind/execute

Huang, Suya wrote > Hello, > > I am using Pgbadger to analyze the postgresql database log recently and > noticed a section "Prepared queries ratio". For my report, it has: > > 1.03 as Ratio of bind vs prepare > 0.12% Ratio between prepared and "usual" statements > > I'm trying to understand what

Re: [PERFORM] group commit

Evgeniy Shishkin wrote > Hi, > > i just wanted to know if group commit (as described in > https://wiki.postgresql.org/wiki/Group_commit ) was committed. I guess that depends on whether this comment in the 9.2 release notes covers the same material described in the linked wiki page (I would presu

Re: [PERFORM] how do functions affect query plan?

常超 wrote > Hi,all > I have a table to save received measure data. > > > CREATE TABLE measure_data > ( >   id serial NOT NULL, >   telegram_id integer NOT NULL, >   measure_time timestamp without time zone NOT NULL, >   item_id integer NOT NULL, >   val double precision, >   CONSTRAINT measure_dat

[PERFORM] Re: recently and selectively slow, but very simple, update query....

Stelios Mavromichalis wrote > as a prior step to dump/restore i am thinking of deleting and re-inserting > that particular row. that might share some light you think? I still dislike the randomness of the unresponsiveness... Every time you perform an update you "delete and insert" that row - that

[PERFORM] Re: recently and selectively slow, but very simple, update query....

Stelios Mavromichalis wrote >> the load of the machine is also low (like 0.2). Which means little if the update is waiting for a lock to be released by one other process; which is more likely the situation (or some other concurrency contention) especially as you said that this particular user gene

[PERFORM] Re: PostgreSQL's query planner is using the wrong index, what can I do to improve this situation?

olavgg wrote > I have a table with 4 indexes => > "stock_trade_pkey" PRIMARY KEY, btree (id) > "stock_trade_source_idx" btree (source_id) > "stock_trade_stock_id_time_idx" btree (stock_id, "time") > "stock_trade_time_idx" btree ("time") > > This table store time series data, basica

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

> > > 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 array improve matters? " SELECT ... FROM message WHERE NOT > (1 > = ANY(reader_ids)) ; UPDATE message SET reader_ids = read

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

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 probably won't help but... You have what amounts to a mathematical "spare matrix" problem on your hands... Is there any way to expire messages so that d

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

How does something like: WITH unreads AS ( SELECT messageid FROM message EXCEPT SELECT messageid FROM message_property WHERE personid=1 AND has_read ) SELECT ... FROM unreads JOIN messages USING (messageid) ; perform? David J. -- View this message in context: http://postgresql.1045698.n5.na

Re: [PERFORM] Best practice question

Tory M Blue wrote > Hi > I am going to add a new column to a table for modify_date that needs to be > updated every time the table is updated. Is it better to just update > application code to set the modify_date to current_time, or create a > Before-Update trigger on the table that will update the