Re: [PERFORM] Hi

[David G. Johnston]

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_document WHERE status = 'Pending' union SELECT
> 'Approved', COUNT(*) FROM ud_document WHERE status = 'Approved' union
> SELECT 'Rejected', COUNT(*) FROM ud_document WHERE status = 'Rejected'
> union SELECT 'Payment Due',count(*) from ud_document where payment_status =
> 'Payment Due' union SELECT 'Payment Done' ,count(*) from ud_document where
> payment_status = 'Payment Done'
>
>
>
> And now I want to exclude the uniqueid= '201708141701018' from the above
> query. how it can be ???
>
>
>
​Your use of UNION here seems necessary.  Just write a normal GROUP BY
aggregation query.  You might need to get a bit creative since you are
collapsing status and payment_status into a single column.  "CASE ... WHEN
... THEN ... ELSE ... END" is quite helpful for doing stuff like that.  For
now I'll just leave them as two columns.

​SELECT status, payment_status, count(*)
FROM ud_document
WHERE uniqueid <> '201708141701018'
GROUP BY 1, 2;

David J.

Re: [PERFORM] Unlogged tables

[David G. Johnston]

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
> you can leverage it.
>
>
​Aren't you over-playing the role of the WAL in providing durability.  An
unlogged table remains intact after a clean shutdown and so is "durable" if
one considers the primary "permanence" aspect of the word.

The trade-off the OP wishes for is "lose crash-safety to gain write-once
(to the data files) performance".  Seeming having this on a per-table basis
would be part of the desirability.  It sounds like OP would be willing to
place the table into "read only" mode in order to ensure this - which is
something that is not presently possible.  I could envision that putting an
unlogged table into read-only mode would cause the system to ensure that
the data files are fully populated and then set a flag in the catalog that
informs the crash recovery process to go ahead and omit truncating that
particular unlogged table since the data files are known to be accurate.

David J.

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

[David G. Johnston]

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 reports use
> subqueries each with their own copies of a range clauses on time column, as
> needed to get constraint exclusion reference:
> https://www.postgresql.org/message-id/25076.1366321335%40sss.pgh.pa.us
>
> SELECT * FROM
> (SELECT * FROM t WHERE col>const) a JOIN
> (SELECT * FROM t WHERE col>const) b USING (col)
>
> I'm diagnosing a bad estimate/plan due to excessively high n_distinct
> leading
> to underestimated rowcount when selecting from a small fraction of the
> table
> heirarchy.  This leads intermittently to bad things, specifically a
> cascade of
> misestimates and associated nested loops around millions of rows.
>

​Justin,

I'm not going to be much help personally but I just wanted to say that with
PGCon just completed and Beta1 just starting, combined with the somewhat
specialized nature of the problem, a response should be forthcoming even
though its taking a bit longer than usual.

David J.

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

[David G. Johnston]

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 cores to reduce the time required
> to generate a single plan?
>
>
My understanding, from both list monitoring and the release notes, is that
query parallelization happens only during execution, not planning.  A
single process is still responsible for evaluating all (possibly partial)
plans and picking the best one - flagging those plan steps that can
leverage parallelism for possible execution.

David J.

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

[David G. Johnston]

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 directly see
objects in another.  You need to use something like postgres_fdw to link
current database and the one containing the materialized view together.​

​https://www.postgresql.org/docs/9.6/static/postgres-fdw.html
​
And ensure the proper permissions are setup.

https://www.postgresql.org/docs/9.6/static/sql-grant.html

David J.

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

[David G. Johnston]

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 172.23.110.178
> zzz 172.23.110.177
> aaa 172.23.110.176
> bbb 172.23.111.180
> ccc 172.23.115.26
>

​SELECT ... WHERE substring(ip_addr::text, 1, 10) = '172.23.110'

David J.
 ​

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

[David G. Johnston]

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 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"?
>>
>>
>>
> Yes.  And if you swap it so that the left join is on the integer while IS
> NULL is on the text, that also gets poorly estimated.  Also, if you make
> both column of both tables be integers, same thing--you get bad estimates
> when the join condition refers to one column and the where refers to the
> other.  I don't know why the estimate is poor, but it is not related to the
> types of the columns, but rather the identities of them.
>
>
​I suspect it has to with the lack of a NOT NULL constraint on either
column causing the planner to disregard the potential to implement a LEFT
JOIN using ANTI-JOIN semantics - or, also possible - the form itself is
invalid regardless of the presence or absence of contraints.  IIUC, while a
true anti-join syntax doesn't exist the canonical form for one uses NOT
EXISTS - which would force the author to use only the correct column pair.

David J.
​

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

[David G. Johnston]

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

[David G. Johnston]

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.043..0.043 rows=0 loops=1)

​I'm not seeing how any of the statistic columns would capture a value that
doesn't actually appear in the table...(actual ... row=0)​

Unless there is some prefix matching going on here since the short value is
a substring(1, n) of the longer one which does appear 5 times.

​I guess maybe because the value doesn't appear it uses the index (via IOS)
to confirm absence (or near absence, i.e., 1) while, knowing the larger
value appears 5 times out of 223, it decides a quick table scan is faster
than any form of double-lookup (whether on the visibility map or the heap).

​https://www.postgresql.org/docs/9.6/static/indexes-index-only-scans.html​

​David J.​

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

[David G. Johnston]

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
>
> 
> 
> ---
>  Aggregate  (cost=8.29..8.30 rows=1 width=0) (actual time=0.048..0.048
> rows=1 loops=1)
>Buffers: shared read=2
>->  Index Only Scan using tab_idx1 on tab  (cost=0.27..8.29 rows=1
> width=0) (actual time=0.043..0.043 rows=0 loops=1)
>  Index Cond: (ID = '01625cfa-2bf8-45cf'::text)
>
>

>->  Seq Scan on tab  (cost=0.00..14.79 rows=5 width=0) (actual
> time=0.031..0.108 rows=5 loops=1)
>  Filter: ((ID)::text = '01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea
> '::text)
>  Rows Removed by Filter: 218
>  Buffers: shared hit=12
>  Planning time: 0.122 ms
>  Execution time: 0.180 ms
> (8 rows)
>

​IIRC the only reason the first query cares to use the index is because it
can perform an Index Only Scan and thus avoid touching the heap at all.  If
it cannot avoid touching the heap the planner is going to just use a
sequential scan to retrieve the records directly from the heap and save the
index lookup step.

David J.

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

[David G. Johnston]

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
> > problem is this sentence from the docs:
> > https://www.postgresql.org/docs/current/static/xfunc-volatility.html
>
> > (Stable): "​This category allows the optimizer to optimize multiple calls
> > of the function to a single call"
>


> If PostgreSQL cannot execute it only once at query start then all this
> talk
> > about optimization seems misleading.  At worse there should be an
> sentence
> > explaining when the optimizations noted in that paragraph cannot occur -
> > and probably examples of both as well since its not clear when it can
> occur.
>
> If you want an exact definition of when things will happen or not happen,
> start reading the source code.  I'm loath to document small optimizer
> details since they change all the time.
>

​That would not be a productive exercise for me, or most people who just
want
some idea of what to expect in terms of behavior when they write and use a
Stable function (Immutable and Volatile seem fairly easy to reason about).

Is there anything fatally wrong with the following comprehension?

"""
A STABLE function cannot modify the database and is guaranteed to
return the same results given the same arguments for all rows
within a single statement.

This category allows the optimizer to take an expression of the form
(indexed_column = stable_function(...)) and evaluate stable_function(...)
once at the beginning of the query and use the result to scan
the index. (Since an index scan will evaluate the comparison
value only once, not once at each row, it is not valid to use a VOLATILE
 function in an index scan condition).  ?Note that should an index scan not
be
chosen for the plan the function will be invoked once-per-row?

Expressions of the forms (constant = stable_function()),
and (SELECT stable_function() FROM generate_series(1,5)) are not presently
optimized to a single per-query evaluation.  To obtain the equivalent you
can invoke the function in a sub-query or CTE and reference the result
wherever it is needed.
"""

It probably isn't perfect but if the average user isn't going to benefit
from
anything besides "index_column = function()" with an index plan then the
false hope that is being held due to the use of "allows + in particular"
should probably be dispelled.

Thanks!

David J.

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

[David G. Johnston]

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
> > trivially independent of row data, and appears in a WHERE clause being
> > compared to constants. Why wouldn't the optimizer treat this case the
> > same as IMMUTABLE?
>
> "The same as IMMUTABLE" would be to reduce the function to a constant at
> plan time, which would be the wrong thing.  It would be valid to execute
> it only once at query start, but there's no built-in mechanism for that.
>

​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
problem is this sentence from the docs:

https://www.postgresql.org/docs/current/static/xfunc-volatility.html

(Stable): "​This category allows the optimizer to optimize multiple calls
of the function to a single call"

I read that sentence (and the surrounding paragraph) and wonder why then
doesn't it do so in this case.

If PostgreSQL cannot execute it only once at query start then all this talk
about optimization seems misleading.  At worse there should be an sentence
explaining when the optimizations noted in that paragraph cannot occur -
and probably examples of both as well since its not clear when it can occur.

Some TLC to the docs here would be welcomed.

David J.

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

[David G. Johnston]

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 happening to have other
> cause to convince him.
>
> Let me break it down once again. The experience is as follows:
>
> - partitioning follows the guide
>

​Only somewhat helpful...
​

> - master empty, no indexes
> - child tables have index on field “field”
> - query like
> SELECT * FROM “master” WHERE “field” BETWEEN ‘1' AND ‘2’
> takes more than 100 sec
>

​All retrieved data now exists in cache/buffers...


> - after that my mate adds index on “master”(“field”) — again, all data is
> in child tables
> - same query takes under 1sec
>

​As ​Andreas said if you really want to explore what is happening here you
need to use EXPLAIN ANALYZE.

Given the flow described above I/O retrieval performance differences, or
the attempt to query the table kicking off an ANALYZE, seems like possible
contributing factors.


> Questions I’d love to clarify:
>
> - Q1: is it correct that described situation happens because index created
> on master does account data that is already there in child?
>

​No
​

> - Q2: is it correct that index on master created before inserting record
> to child tables will not take into account this record?
>

Yes
​

> - Q3: are there any other bad sides of indexes on master table?
>

No​

David J.

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

[David G. Johnston]

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 of child​ tables.

In most partitioning schemes the master table is empty so even if it
doesn't have an index on a particular field execution would typically be
quick.  This is why #4 on the page you linked to:

"""
For each partition, create an index on the key column(s), as well as any
other indexes you might want. (The key index is not strictly necessary, but
in most scenarios it is helpful. If you intend the key values to be unique
then you should always create a unique or primary-key constraint for each
partition.)
"""

doesn't say anything about creating other indexes on the master table.  See
#1 in that list for an explicit statement of this assumption.

If the master is not empty, and of considerable size, and the field being
searched is not indexed, then it is unsurprising that the query would take
a long time to execute when obtaining rows from the master table.  If this
is the case then you've gotten away from the expected usage of partitions
and so need to do things that aren't in the manual to make them work.

David J.



David J.

Re: [PERFORM] materialized view order by and clustering

[David G. Johnston]

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?  Would the clustering be preserved?
>
>
​
​The notes on the REFRESH MATERIALIZED VIEW page seem informative to this
question:

​"While the default index for future CLUSTER operations is retained,
REFRESH MATERIALIZED VIEW does not order the generated rows based on this
property. If you want the data to be ordered upon generation, you must use
an ORDER BY clause in the backing query."

​https://www.postgresql.org/docs/9.6/static/sql-refreshmaterializedview.html
​
​


> I'm trying to get a handle on the concept of clustering and how that is
> different than order_by and which would be better and how much advantage it
> really gets me.
>

​
CLUSTER is a physical property
​(table only) ​
while ORDER BY is a logical one
​ (view only)

With respect to materialized views - which act as both table and view - the
logically ordered view data gets saved to the physical table thus making
the table clustered on whatever order by is specified.

​David J.
​
​

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

[David G. Johnston]

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 function
>
>
​I have no difficulty using arrays in functions.

As for "other methods" - you can use CTE (WITH) to create a truly local
table - updating the catalogs by using a temp table is indeed quite
expensive.

WITH vals AS  ( VALUES (1, 'lw'), (2, 'lw2') )
SELECT * FROM vals;

David J.

Re: [PERFORM] Performance problems with 9.2.15

[David G. Johnston]

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

[David G. Johnston]

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 push_topic
> (cost=0.42..8.44 rows=1 width=103) (actual time=0.117..0.121 rows=1 loops=1)
> >   Index Cond: ((guid)::bpchar =
> 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5'::bpchar)
> >   Buffers: shared hit=3 read=1
> > Total runtime: 0.191 ms
>
> > However when I run the exact query through a different application
> (CodeSynthesis ORM) the query is very slow (~ 115ms logged)
> > I noted this is due to a sequential scan happening on the table instead
> of an index scan.
>
> It looks like what that app is actually issuing is something different
> from what you tested by hand, to wit
>
> select * from push_topic where guid =
> 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5'::text
>
> which causes the comparison to be resolved as texteq not bpchareq, ie you
> effectively have
>
> select * from push_topic where guid::text =
> 'DD748CCD-B8A4-3B9F-8F60-67F1F673CFE5'::text
>
> and that doesn't match a bpchar index.  If you can't persuade the app to
> label the comparison value as bpchar not text, the easiest fix would be
> to create an additional index on "guid::text".
>

​Or, better, persuade the app to label the value "
​
public.push_guid
​" since that is the column's type​...a type you haven't defined for us.
If you get to add explicit casts this should be easy...but I'm not familiar
with the framework you are using.

David J.

Re: [PERFORM] pg_restore seems very slow

[David G. Johnston]

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
> pretty powerful machines (40-core Xeon workstations with decent hardware
> across the board). While the DBs vary in size, I'm working right now with
> one that is roughly 50 tables and probably 75M rows, and is about 300MB on
> disk when exported via pg_dump.
>
> I am exporting and restoring using these commands (on separate sytems):
> pg_dump -F c mydb > mydb.dump
> pg_restore -C -j 10 mydb.dump
>
> The dump process runs in about a minute and seems fine. The restore
> process has already been running for around 7 hours.
>
> Yesterday, I tried restoring a larger DB that is roughly triple the
> dimensions listed above, and it ran for over 16 hours without completing.
>
> I followed the advice given at
> http://www.databasesoup.com/2014/09/settings-for-fast-pgrestore.html and
> set the conf settings as directed and restarted the server.
>
> You can see in the command line that I am trying to use the -j parameter
> for parallelism, but I don't see much evidence of that in Task Manager. CPU
> load is consistently 1 or 2% and only a couple cores seem to be doing
> anything, there certainly aren't 10 cpu-bound cores. I'm not sure where to
> look for pg_restore's disk I/O, but there is an entry for pg_restore in
> Task Manager/Processes which shows almost no I/O Read Bytes and 0 I/O Write
> Bytes. Since that's just the parent process that might make sense but I
> don't see much activity elsewhere either.
>
> Is there something simple that I am missing here? Does the -j flag not
> work in 8.4 and I should use --jobs? It just seems like none of the CPU or
> RAM usage I'd expect from this process are evident, it's taking many times
> longer than I would expect, and I don't know how to verify if the things
> I'm trying are working or not.
>
> Any insight would be appreciated!
>
>
​Did any databases restore properly?

Are there any message in logs or on the terminal​?  You should add the
"--verbose" option to your pg_restore command to help provoke this.

-C can be problematic at times.  Consider manually ensuring the desired
target database exists and is setup correctly (matches the original) and
then do a non-create restoration to it specifically.

-j should work fine in 8.4 (according to the docs)

You need to get to a point where you are seeing feedback from the
pg_restore process.  Once you get it telling you what it is doing (or
trying to do) then diagnosing can begin.

​David J.
​

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

[David G. Johnston]

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 the field in question, which makes LIKE '%%'
> searches run very fast.  The problem is the NOT LIKE phrases, which (as
> would be expected) force a sequential scan.  Being that we're talking about
> millions of records, this is not desirable.
> Here's the question...
> Is there a way, *using a single query*, to emulate the process of running
> the LIKE part first, then running the NOT LIKE just on those results?  I
> can accomplish this in a multi-step process by separating the single query
> into two queries, populating a temporary table with the results of the
> LIKEs, then running the NOT LIKEs on the temporary table.  For various
> reasons, this is not the ideal solution for me.
> Or is there another approach that would accomplish the same thing with the
> same level of performance?
>


Try AND...where col like '' and col not like ''

Or a CTE (with)

With likeqry as ( select where like )
Select from likeqry where not like

(sorry for brevity but not at a pc)

David J.

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

[David G. Johnston]

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 >> >:
>>>
>>>> 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: array size exceeds the maximum allowed (1073741823)
>>>>>
>>>>> ​https://www.postgresql.org/about/​
>>>>
>>>> ​Maximum Field Size: 1 GB​
>>>>
>>>
>>> It means a json cannot exceed 1GB in postgresql, right ?
>>>
>>
>> ​Yes​
>>
>>
>>> Then I must build it with an external tool ?
>>> ​
>>>
>>>
>>
>> ​​You have to do something different.  Using multiple columns and/or
>> multiple rows might we workable.
>>
>
> ​Certainly. Kind of disappointing, because I won't find any json builder
> as performant as postgresql.​
>
> ​
>
> Will this 1GO restriction is supposed to increase in a near future ?​
>
>
There has been zero chatter on the public lists about increasing any of the
limits on that page I linked to.

David J.
​

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

[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 :
>
>> 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: array size exceeds the maximum allowed (1073741823)
>>>
>>> ​https://www.postgresql.org/about/​
>>
>> ​Maximum Field Size: 1 GB​
>>
>
> It means a json cannot exceed 1GB in postgresql, right ?
>

​Yes​


> Then I must build it with an external tool ?
> ​
>
>

​​You have to do something different.  Using multiple columns and/or
multiple rows might we workable.

David J.

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

[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 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: array size exceeds the maximum allowed (1073741823)
>
> ​https://www.postgresql.org/about/​

​Maximum Field Size: 1 GB​

​It doesn't matter that the data never actually is placed into a physical
table.

David J.

Re: [PERFORM] similarity and operator '%'

[David G. Johnston]

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 street % $2
> and (zip % $3 or city % $4)
> or similarity(name, $1) > 0.8
>
> which means: take all addresses where name, street, zip and city have
> little similarity _plus_ all addresses where the name matches very good.
>
>
> The only way I found, was to create a temporary table from the first
> query, change the similarity value with set_limit() and then select the
> second query UNION the temporary table.
>
> Is there a more elegant and straight forward way to achieve this result?
>

​Not that I can envision.

You are forced into using an operator due to our index implementation.

You are thus forced into using a GUC to control the parameter that the
index scanning function uses to compute true/false.

A GUC can only take on a single value within a given query - well, not
quite true[1] but the exception doesn't seem like it will help here.

Th
us you are consigned to​

​using two queries.

*​A functional index​ doesn't work since the second argument is query
specific

[1]​ When defining a function you can attach a "SET" clause to it; commonly
used for search_path but should work with any GUC.  If you could wrap the
operator comparison into a custom function you could use this capability.
It also would require a function that would take the threshold as a value -
the extension only provides variations that use the GUC.

I don't think this will use the index even if it compiles (not tested):

CREATE FUNCTION similarity_80(col, val)
RETURNS boolean
SET similarity_threshold = 0.80
LANGUAGE sql
AS $$
​SELECT ​col % val;
$$;

​David J.​

Re: [PERFORM] Odd behavior with indices

[David G. Johnston]

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   2014935   4563638  132955919 7
>
>
​Ok...again its beyond my present experience ​but its what the planner
thinks about the distribution, and not what actually is present, that
matters.

David J.

Re: [PERFORM] Odd behavior with indices

[David G. Johnston]

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 "return all
> sites with data" and "return all parameters with data". The site table
> has only 200 rows or so, and the parameter table has only 100 or so rows.
>
> The site query is fast and uses the index:
>
> EXPLAIN ANALYZEselect *from sitewhere exists (
>   select 1 from datavalue
>   where datavalue.site_id = site.id limit 1);
>
> Seq Scan on site  (cost=0.00..64.47 rows=64 width=113) (actual 
> time=0.046..1.106 rows=89 loops=1)
>   Filter: (SubPlan 1)
>   Rows Removed by Filter: 39
>   SubPlan 1
> ->  Limit  (cost=0.44..0.47 rows=1 width=0) (actual time=0.008..0.008 
> rows=1 loops=128)
>   ->  Index Only Scan using ix_datavalue_site_id on datavalue  
> (cost=0.44..8142.71 rows=248930 width=0) (actual time=0.008..0.008 rows=1 
> loops=128)
> Index Cond: (site_id = site.id)
> Heap Fetches: 0
> Planning time: 0.361 ms
> Execution time: 1.149 ms
>
> The same query for parameters is rather slow and does NOT use the index:
>
> EXPLAIN ANALYZEselect *from parameterwhere exists (
>   select 1 from datavalue
>   where datavalue.parameter_id = parameter.id limit 1);
>
> Seq Scan on parameter  (cost=0.00..20.50 rows=15 width=2648) (actual 
> time=2895.972..21331.701 rows=15 loops=1)
>   Filter: (SubPlan 1)
>   Rows Removed by Filter: 6
>   SubPlan 1
> ->  Limit  (cost=0.00..0.34 rows=1 width=0) (actual 
> time=1015.790..1015.790 rows=1 loops=21)
>   ->  Seq Scan on datavalue  (cost=0.00..502127.10 rows=1476987 
> width=0) (actual time=1015.786..1015.786 rows=1 loops=21)
> Filter: (parameter_id = parameter.id)
> Rows Removed by Filter: 7739355
> Planning time: 0.123 ms
> Execution time: 21331.736 ms
>
> What the deuce is going on here? Alternatively, whats a good way to do
> this?
>
> Any help/guidance appreciated!
>
>
>
> Some of the table description:
>
> \d datavalue
>
> id BIGINT DEFAULT nextval('datavalue_id_seq'::regclass) NOT NULL,
> value DOUBLE PRECISION NOT NULL,
> site_id INTEGER NOT NULL,
> parameter_id INTEGER NOT NULL,
> deployment_id INTEGER,
> instrument_id INTEGER,
> invalid BOOLEAN,
> Indexes:
> "datavalue_pkey" PRIMARY KEY, btree (id)
> "datavalue_datetime_utc_site_id_parameter_id_instrument_id_key" UNIQUE 
> CONSTRAINT, btree (datetime_utc, site_id, parameter_id, instrument_id)
> "ix_datavalue_instrument_id" btree (instrument_id)
> "ix_datavalue_parameter_id" btree (parameter_id)
> "ix_datavalue_site_id" btree (site_id)
> "tmp_idx" btree (site_id, datetime_utc)
> Foreign-key constraints:
> "datavalue_instrument_id_fkey" FOREIGN KEY (instrument_id) REFERENCES 
> instrument(id) ON UPDATE CASCADE ON DELETE CASCADE
> "datavalue_parameter_id_fkey" FOREIGN KEY (parameter_id) REFERENCES 
> parameter(id) ON UPDATE CASCADE ON DELETE CASCADE
> "datavalue_site_id_fkey" FOREIGN KEY (site_id) REFERENCES 
> coastal.site(id) ON UPDATE CASCADE ON DELETE CASCADE
> "datavalue_statistic_type_id_fkey"
>
>
> ​I'm not great with the details but the short answer - aside from the fact
that you should consider increasing the statistics on these columns - is
that at a certain point querying the index and then subsequently checking
the table for visibility is more expensive than simply scanning and then
discarding ​the extra rows.

The fact that you could perform an INDEX ONLY scan in the first query makes
that cost go away since no subsequent heap check is required.  In the
parameters query the planner thinks it needs 1.5 million of the rows and
will have to check each of them for visibility.  It decided that scanning
the entire table was more efficient.

The LIMIT 1 in both queries should not be necessary.  The planner is smart
enough to stop once it finds what it is looking for.  In fact the LIMIT's
presence may be a contributing factor...but I cannot say for sure.

A better query seems like it would be:

WITH active_sites AS (
SELECT DISTINCT site_id FROM datavalues;
)
SELECT *
FROM sites
JOIN active_sites USING (site_id);

David J.

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

[David G. Johnston]

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;
>
> The indexes are in place but I've noticed that it is only used after the
> first execution of the function.
>

​How do you know this?​

I think that the problem is that Postgres isn't getting the best execution
> plan at first because of a parameter that it is highly exclusive in the
> majority of the cases, but it can be not as good sometimes. We can't change
> the way we call the function to a plain sql statement or a view because we
> can't change the application code itself.
>
> When I test with EXPLAIN ANALYZE after the first execution, the query runs
> really fast but the aplication sessions call the function only once and
> then are terminated. I need that the first execution use the actual
> optimized plan.
>
> We tried messing around with the connector driver that manage the
> connection pooling to issue a DISCARD TEMP instead of DISCARD ALL, so it
> could keep the cached plan of the sessions and the performance improved a
> lot, but I don't want to do that in a production environment.
>

Given the constraints you've listed this seems like it might be your only
avenue of improvement.​  Your problem that the performance improvement is
seen due to caching effects.  If you throw away the cache you loose the
improvement.


> I've tried to change the language to a sql function but it didn't help as
> the execution time didn't drop after the first execution.
>

​Yes, this likely would make thing worse...depending upon how it is called.

I've tried to add the "SET LOCAL join_collapse_limit = 1" too but it
> appears it doesn't work inside a function;
>

​I wouldn't expect that parameter to have any effect in this scenario.

Here is the function code:
>
> CREATE OR REPLACE FUNCTION public.ap_keepalive_geteqpid_veiid(
> IN tcbserie bigint,
> IN protocolo integer)
>   RETURNS TABLE(eqpid integer, veiid integer, tcbid integer, veiplaca
> character varying, veiproprietariocliid integer, tcbtppid integer,
> tcbversao character, veirpmparametro double precision, tcbconfiguracao
> bigint, tcbevtconfig integer, veibitsalertas integer, sluid integer, harid
> integer) AS
> $BODY$
> BEGIN
>
> RETURN QUERY
> SELECT teqp.eqpID,
> teqp.eqpveiID AS veiID,
> tcb.tcbID,
> tvei.veiPlaca,
> tvei.veiProprietariocliID,
> tcb.tcbtppID,
> tcb.tcbVersao,
> tvei.veiRPMParametro,
> COALESCE(COALESCE(NULLIF(tcb.tcbConfiguracao, 0),
> tcc.clcConfiguracaoBitsVeic), 0) AS tcbConfiguracao,
> COALESCE(tcb.tcbevtConfig, 0) AS tcbevtConfig,
> COALESCE(tvei.veiBitsAlertas, 0) AS veiBitsAlertas,
> COALESCE(tvei.veisluID, 0) AS sluID,
> COALESCE(tcb.tcbharID, 0) AS harID
> FROM TabComputadorBordo tcb
> INNER JOIN TabEquipamento teqp ON teqp.eqptcbID = tcb.tcbID
> INNER JOIN TabPacoteProduto tpp ON teqp.eqptppID = tpp.tppID
> INNER JOIN TabVeiculos tvei ON teqp.eqpveiID = tvei.veiID
> LEFT JOIN TabCliente tcli ON tcli.cliid = tvei.veiProprietariocliID
> LEFT JOIN TabClienteConfig tcc ON tcc.clcCliID = tcli.cliID
> WHERE   tcb.tcbserie = $1
> AND teqp.eqpAtivo = 1
> AND tpp.tppIDProtocolo = $2
> AND tvei.veiBloqueioSinal = 0;
>
> END
> $BODY$
>   LANGUAGE plpgsql VOLATILE
>   COST 1
>   ROWS 1;
>
> Execution plan in the first execution:
>

​You likely could make this STABLE instead of VOLATILE; though that doesn't
solve your problem.​


> "Function Scan on ap_keepalive_geteqpid_veiid  (cost=0.25..0.26 rows=1
> width=116) (actual time=3.268..3.268 rows=1 loops=1)"
> "Planning time: 0.032 ms"
> "Execution time: 3.288 ms"
>
> Second execution:
>
> "Function Scan on ap_keepalive_geteqpid_veiid  (cost=0.25..0.26 rows=1
> width=116) (actual time=0.401..0.402 rows=1 loops=1)"
> "Planning time: 0.058 ms"
> "Execution time: 0.423 ms"
>
>
​I'm doubting the query inside of the function is the problem here...it is
the function usage itself.  Calling a function has overhead in that the
body of function needs to be processed.  This only has to happen once per
session.  The first call of the function incurs this overhead while
subsequent calls do not.

Pending others correcting me...I fairly certain regarding my conclusions
though somewhat inexperienced in doing this kind of diagnostics.

David J.

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

[David G. Johnston]

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(),
>   attachment_name character varying NOT NULL,
>   attachment_bytes_size integer NOT NULL,
>   attachment_bytes bytea NOT NULL,
>   CONSTRAINT attachment_pkey PRIMARY KEY (attachment_id)
> );
>
> I do lookups on this table based on the md5 of the attachment_bytes
> column, so I added an index:
> CREATE INDEX idx_attachment_bytes_md5 ON attachment
> ((md5(attachment_bytes)::uuid));
>
> Queries like this are sped up by the index no problem:
> SELECT attachment_id
> FROM attachment
> WHERE md5(attachment_bytes)::uuid = 'b2ab855ece13a72a398096dfb6c832aa';
>
> But if I wanted to return the md5 value, it seems to be totally unable to
> use an index only scan:
> SELECT md5(attachment_bytes)::uuid
> FROM attachment;
>
>
Ok.

Any reason not to add the uuid column to the table?

AFAIK The system is designed to return data from the heap, not an index.
While it possibly can in some instances if you need to return data you
should store it directly in the table.

David J.

Re: [PERFORM] Partition Constraint Exclusion Limits

[David G. Johnston]

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 constraint.  I'm curious if there is a way to get it
> to work a little more generally though.
>
> For example my CHECK constraint (see code below) specifying a hard-coded
> field value works well (#1 and #2).  Specifying a function that returns a
> value even though it is the appropriate type scans all of the partitions
> (#3) unfortunately.  Likewise any join, CTE, or sub-query expression, even
> for a single row that returns the correct type also results in a scan of
> all of the partitions.
>
> I was curious if there was a way specifically to get #3 to work as the
> WHERE predicate in this case is stored as an integer but the table itself
> is partitioned by the appropriate date type.  I believe I could work around
> this issue with dynamic sql in a function but there are lots of cases of
> this type of simple conversion and I wanted to avoid the maintenance of
> creating a function per query.
>

​Short answer, no.

The planner has the responsibility for performing constraint exclusion and
it only has access to constants during its evaluation.  It has no clue what
kind of transformations a function might do.  Various other optimizations
are indeed possible but are not presently performed.

​So, #3 (
to_date(201406::text||01::text, 'MMDD');
​) ​
is down-right impossible given the present architecture
​; and likely any future architecture.

With #4 (
explain analyze select count(1) from ptest.tbl where dt = (select
'2014-06-01'::date);
​) ​
in theory the re-write module could recognize and re-write this remove the
sub-select.
​  But likely real-life is not so simple otherwise the query writer likely
would have simply done is directly themself.

​
​
​
​In a partitioning scheme the partitioning data has to be injected into the
query explicitly so that it is already in place before the planner receives
the query.  Anything within the query requiring "execution" is handled by
the executor and at that point the chance to exclude partitions has come
and gone.

David J.

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

[David G. Johnston]

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.
>
>
​The only index that matters here is the pkey on document.  The problem is
the failure to exit the nested loop once 1,000 translations have been
gathered.  Translation is related to document via key - hence the nested
loop.  A hashing-based plan would make use of the secondary indexes but
likely would not be particularly useful in this query (contrary to my
earlier speculation).

Here's the query plan:
>
> 
>
> What's so special about that *date_last_updated* condition that makes it so
> slow to use? Is it because it involves the *date()* function call that it
> makes it difficult for the planner to guess the data distribution in the
> DOCUMENT table?
>

What happens if you pre-compute the date condition and hard code it?

​
​David J.
​

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

[David G. Johnston]

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" config
> > parameters (shared buffers, work mem)?
>
> I am running 9.4.4, here are the basic config parameters:
>
> work_mem = 32 MB
> shared_buffers = 8196 MB
> temp_buffers = 8 MB
> effective_cache_size = 4 GB
>
> I have run ANALYZE on all tables prior to running the queries. The query
> plan for the fast version (without the WHERE clause) follows:
>
> 
>
> What I don't understand is the difference between the inner NESTED LOOP
> between the slow and the fast query plan. In the fast one, both index scans
> have 1000 as the actual row count. I would expect that, given the LIMIT
> clause. The slow query plan, however, shows ~ 75 000 000 as the actual row
> count. Is the extra WHERE condition the only and *plausible* explanation
> for
> this difference?
>
>
​In the slow query it requires evaluating every single document to
determine which of the 75 million translations can be discarded; after
which the first 1000 when sorted by translation id are returned.

In the first query the executor simply scans the translation index in
ascending order and stops after retrieving the first 1,000.

What you are expecting, I think, is for that same process to continue
beyond 1,000 should any of the first 1,000 be discarded due to the
corresponding document not being updated recently enough, until 1,000
translations are identified.  I'm not sure why the nested loop executor is
not intelligent enough to do this...

The important number in these plans is "loops", not "rows"

David J.

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

[David G. Johnston]

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)
>   fk_id_translation_unit (indexed)
>
> Table sizes:
>  DOCUMENT: 381 000
>  EXTERNAL_TRANSLATION_UNIT: 76 000 000
>  EXTERNAL_TRANSLATION: 76 000 000
>
> Now the following query takes about 36 minutes to finish:
>
>  SELECT u.id AS id_external_translation_unit,
> r.id AS id_external_translation,
> u.fk_id_language AS fk_id_source_language,
> r.fk_id_language AS fk_id_target_language,
> doc.fk_id_job
>  FROM "EXTERNAL_TRANSLATION_UNIT" u
>  JOIN "DOCUMENT" doc ON u.fk_id_document = doc.id
>  JOIN "EXTERNAL_TRANSLATION" r ON u.id = r.fk_id_translation_unit
>  WHERE doc.date_last_updated >= date(now() - '171:00:00'::interval)
>  ORDER BY r.id LIMIT 1000
>
> This is the query plan:
>
> 
>
> If I remove the WHERE condition, it returns immediately.
>
>
​So does  "SELECT 1;" - but since that doesn't give the same answer it is
not very relevant.​


> Am I doing something obviously wrong?
>

​Not obviously...​


> Thank you for any ideas.
>

​Consider updating the translation tables at the same time the document
table is updated.  That way you can apply the WHERE and ORDER BY clauses
against the same table.

​I presume you've run ANALYZE on the data.

I would probably try something like:

WITH docs AS ( SELECT ... WHERE date > ...)
SELECT ... FROM (translations join translation_unit) t
WHERE EXISTS (SELECT 1 FROM docs WHERE t.doc_id = docs.doc_id)
ORDER BY t.id LIMIT 1000

You are trying to avoid the NESTED LOOP and the above has a decent chance
of materializing docs and then building either a bit or hash map for both
docs and translations thus performing a single sequential scan over both
instead of performing 70+ million index lookups.

Take this with a grain of salt as my fluency in this area is limited - I
tend to work with trial-and-error but without data that is difficult.

I'm not sure if the planner could be smarter because you are asking a
question it is not particularly suited to estimating - namely cross-table
correlations.  Rethinking the model is likely to give you a better outcome
long-term though it does seem like there should be room for improvement
within the stated query and model.

As Tomas said you likely will benefit from increased working memory in
order to make materializing and hashing/bitmapping favorable compared to a
nested loop.

David J.

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

[David G. Johnston]

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
>> WHERE reference_id EXISTS/IN/JOIN)​
>>
>> ​src
>> ​LEFT JOIN type1 USING (reference_id)
>> LEFT JOIN type2 USING (reference_id)
>> [...]
>>
>
>
​Place ^ in a CTE named (find_all)​


> there are no tables where reference_id is a pk, I could create one or do :
> select reference_id from ( values (..), (...), (...)  )
>
> the tricky part with the join (and where I was not clear about it in my
> original description) is that a reference_id  can match in multiple tables
> (eg. it can be a fk in type1 and type2), so it then becomes a bit harder to
> collect all the common attributes and 'types' when doing joins like this.
>
> For example let's assume there is a group_id to be be retrieved among all
> tables as a common attribute:
>
> if reference_id was existing only in one table, I could do
> coalesce(type1.group_id, ... type5.group_id) as group_id in the main select
> however that would not work in this case.
>
>
​WITH find_all (reference_id, type_identifier, type_id) AS ( ... )
SELECT ​type_identifier, array_agg(reference_id), array_agg(type_id)
FROM find_all
WHERE type_identifier IS NOT NULL
GROUP BY type_identifier

​find_all will return at least one row, possibly empty if no matches are
present, and will return multiple rows if more than one matches.  You can
use array_agg as shown, or play around with custom composite types, ​or
even build a JSON document.

David J.

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

[David G. Johnston]

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, [...] FROM table_where_referenced_id_is_a_pk
WHERE reference_id EXISTS/IN/JOIN)​

​src
​LEFT JOIN type1 USING (reference_id)
LEFT JOIN type2 USING (reference_id)
[...]

Or consider whether PostgreSQL Inheritance would work - though basically
its a friendly API over the "UNION ALL" query you proposed.

David J.

Re: [PERFORM] Insert vs Update

[David G. Johnston]

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 schema is not important, it's really the write speed
>> that matters. So I was just wondering given the INSERT or UPDATE approach
>> (with no indexed data being changed) if one is likely to be substantially
>> faster than the other.
>>
>>
> As I understand how ACID compliance is done, updating a record will
> require updating any indexes for that record, even if the index keys are
> not changing.  That's because any pending transactions still need to be
> able to find the 'old' data, while new transactions need to be able to find
> the 'new' data.  And ACID also means an update is essentially a
> delete-and-insert.
>

​I might be a bit pedantic here but what you describe is a byproduct of the
specific​ implementation that PostgreSQL uses to affect Consistency (the C
in ACID) as opposed to a forgone outcome in being ACID compliant.

http://www.postgresql.org/docs/9.4/static/mvcc-intro.html

I'm out of my comfort zone here but the HOT optimization is designed to
leverage the fact that an update to a row that does not affect indexed
values is able to leave the index alone and instead during index lookup the
index points to the old tuple, notices that there is a chain present, and
walks that chain to find the currently active tuple.

In short, if the only index is a PK an update of the row can avoid touching
that index.

I mentioned that going from NULL to Not NULL may disrupt this but I'm
thinking I may have mis-spoken.

Also, with separate tables the amount of data to write is going to be less
because you'd have fewer columns on the affected tables.

While an update is a delete+insert a delete is mostly just a bit-flip
action - at least mid-transaction.  Depending on volume, though, the
periodic impact of vaccuming may want to be taken into consideration.

David J.

Re: [PERFORM] Insert vs Update

[David G. Johnston]

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 prove that an alternative arrangement was more
performant.

A single optional date with meta-data embedded in the column name​

​is usually workable but if you then have a bunch of other columns with
name like:

preparation_date, preparation_col1, preparation_col2, consumed_col1,
consumed_col2, consumed_date
​

​I would find that to be undesirable.

You may be able to put Table Inheritance to good use here...

I do not know (but doubt) if HOT optimization works when going from NULL to
non-NULL since the former is stored in a bitmap while the later occupies
normal relation space and thus the update would likely end up writing an
entirely new​ record upon each event category recording.

David J.

Re: [PERFORM] Insert vs Update

[David G. Johnston]

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 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 UPDATE data. And if there is a performance
>>> difference is it substantial?
>>>
>>
>> This seems odd.  If you have an option to update but choose to insert
>> what becomes of the other record?
>>
>
>
> Consider the two pseudo-schemas, I'm just making this up for example
> purposes:
>
> SCHEMA A
> =
> meal(id SEQUENCE,user_id, started DEFAULT NOW())
> meal_prepared(ref_meal_id, prepared DEFAULT NOW())
> meal_abandoned(ref_meal_id, abandoned ...)
> meal_consumed(ref_meal_id, consumed ...)
> etc.
>
> Then in response to different meal events you always have an insert.
>
> aMealId = INSERT INTO meal(user_id) VALUES (aUserId);
>
> When preparation starts:
>
> INSERT INTO meal_prepared(ref_meal_id) VALUES (aMealId);
>
> And so on for each event.
>
> Compare that to this:
>
> SCHEMA B
> =
> meal_event(id, started, prepared, abandoned, consumed, ...)
>
> The start of the meal is an INSERT:
>
> aMealId = INSERT INTO meal_event(user_id, started) VALUES (aUserId, NOW());
>
> When preparation starts:
>
> UPDATE meal_event SET prepared = NOW() WHERE id = aMealId;
>
> And so on.
>
> Basically the same data, in one case you always do inserts and add new
> tables for new events. In the other case you only insert once and then
> update for each state, then you add columns if you have new states.
>
> As I said this is just an example. But in SCHEMA A you have only inserts,
> lots of tables and in SCHEMA B you have a lot of updates and a lot of
> possibly NULL columns if certain events don't occur.
>
> Is that more clear?
>
>
​Yes, you are trying to choose between a bunch of one-to-one (optional)
relationships versus adding additional columns to a table all of which can
be null.

​I'd argue that neither option is "normal" (in the DB normalization sense).

CREATE TABLE meal (meal_id bigserial)
CREATE TABLE meal_even​t_type (meal_event_id bigserial)
CREATE TABLE meal_event (meal_id bigint, meal_event_id bigint, occurred_at
timestamptz)

​So now the decision is one of how to denormalize.  materialzed views and
two ways to do so.  The specific solution would depend in part on the final
application queries that you need to write.

If you do want to model the de-normalized form, which I would likely be
tempted to do given a fixed set of "events" that do not require additional
related attributes, would be to place the few event timestamps on the main
table and UPDATE them to non-null.

In the normal form you will likely find partial indexes to be quite useful.

David J.
​

Re: [PERFORM] Insert vs Update

[David G. Johnston]

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 UPDATE data. And if there is a performance
> difference is it substantial?
>

This seems odd.  If you have an option to update but choose to insert what
becomes of the other record?

Re: [PERFORM] Insert vs Update

[David G. Johnston]

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 or UPDATE data. And if there is a performance
> difference is it substantial?
>
> I have a situation where I can easily do one or the other to the same
> effect. For example, I have a journaling schema with a limited number of
> "states" for an "entry". Currently each state is it's own table so I just
> insert them as they occur. But I could easily have a single "entry" table
> where the row is updated with column information for states (after the
> entry's initial insertion).
>
> Not a big deal but since it's so easy for me to take either approach I was
> wondering if one was more efficient (for a large DB) than another.
>
>
​There is HOT (heap only tuple?) optimization that can occur if only
non-indexed data is altered.  I do not recall the specifics.

Dave
​

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

[David G. Johnston]

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 would take
effect after a reboot - though most are used on the very next query that
runs.

The vacuum would indeed likely account for the gains - there being
significantly fewer ​dead/invisible rows to have to scan over and discard
while retrieving the live rows that fulfill your query.

David J.

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

[David G. Johnston]

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.
>
> However, once an hour, the (very complex) query writes A LOT on the disk
> (more than 95 Gb !!!)
> We have 64 Gb of RAM and this is our config :
>
>
> And my error on the query is :
>
>
> Do you know how to solve this problem ?
>
> Best regards,
> Benjamin.
>
>
>
> --
> View this message in context:
> http://postgresql.nabble.com/How-to-reduce-writing-on-disk-90-gb-on-pgsql-tmp-tp5852321.html
> Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
>
>
> --
> 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] Fastest way / best practice to calculate "next birthdays"

[David G. Johnston]

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 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 boundaries but do start on Monday.
>
> There is the year start/end boundary conditions to worry about there.
>
> If the current week covers Dec28-Jan02 then week of year won't help for
> a birthday on Jan01 or Jan02 if 'today' is in the Dec portion.  Ditto
> for birthday in Dec portion when 'today' is in the Jan portion.
>
>
You need to read the documentation regarding ISO year and ISO week more
carefully.  There is no issue with years only ensuring that your definition
of week starts with Monday and contains 7 days.  The ISO year for January
1st can be different than the Gregorian year for the same.

David J.

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

[David G. Johnston]

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 boundaries but do start on Monday.

David J.

Re: [PERFORM] unlogged tables

[David G. Johnston]

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 the slave to basically "rewrite" its existing
>> table by pointing to the newly supplied version.  Some kind of "CREATE
>> STATIC TABLE" and "PUSH STATIC TABLE TO {all | replica name}" command
>> combo...though ideally with less manual intervention...
>>
>
> You still have the same problem of knowing if someone has scribbled on the
> data since the last checkpoint.
>

​That seems like an automation concern though...the more limited idea was
to simply have a means for a table to exist on the master and allow the
user to cause an exact copy of that table to appear on a replica via direct
data transfer (i.e., without need to create a backup/dump).  If the table
already exists on the replica the existing version remains as-is until the
new table is fully push and then a filenode pointer update happens.  If
changes are made to the master the two tables will remain diverged until a
new push occurs.​

I imaging this same idea could be handled external to the database though
I'm don't know enough to comment on the specific technical merits of each.​


> There's been recent discussion of adding support for read-only tables. If
> we had those, we might be able to support something like...
>
> INSERT INTO unlogged;
> ALTER TABLE unlogged SET READ ONLY;
> CHECKPOINT;
> /* take backup */
>
> This should be safe as long as we WAL log changes to read-only status
> (which presumably we would).
>
> How much work that would entail though, I don't know.
>
> Ultimately you still have to get the data over to the other machine
> anyway. ISTM it'd be a LOT more useful to look at ways to make the WAL
> logging of bulk inserts (and especially COPY into a known empty table) a
> lot more efficient.
>
>
​Jeff Janes makes a comment about wanting "...to avoid blowing out the log
archive..."; which I also don't quite follow...

WAL does seem to be designed to solve a different problem that what is
described here - lots of small changes versus few large changes.  Improving
WAL to move the size at which small becomes large is a win but another
channel designed for few large changes may be less complex to implement.
The current work in logical replication likely has merit here as well but
my familiarity with that technology is fairly limited.

David J.

Re: [PERFORM] unlogged tables

[David G. Johnston]

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 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 periodically flush data to disk and in case of crush
>>>> postgres to load only the data that existed at last flush? The periodic
>>>> flush could be configurable, for example every 30 minutes or after x
>>>> rows
>>>> updated/inserted.
>>>>
>>>
>>> There is no such facility implemented for UNLOGGED TABLEs. That could be
>>> a feature request though.
>>>
>>
> One way would be to lock dirty buffers from unlogged relations into
> shared_buffers (which hardly seems like a good thing) until the start of a
> "super-checkpoint" and then write them all out as fast as possible (which
> kind of defeats checkpoint_completion_target).  And then if the crash
> happened during a super-checkpoint, the data would still be inconsistent
> and need to be truncated.
>
>
>>
>>>
>> Well, that is half right anyway.  UNLOGGED tables obey checkpoints just
>> like any other table.
>>
>
> Do they?  I thought they only obeyed shutdown checkpoints, not online
> checkpoints.  I do remember some changes around this area, but none that
> completely reverted that logic.
>
>
​I vaguely recall that conversation now...I'm not positive on the exact
mechanics here and, as it pertains to the OP, the difference you describe
is immaterial since in either case the status quo mandates an "all or
nothing" approach to an unlogged table's contents.​



>
>
>> The missing feature is an option to leaved restored the last checkpoint.
>> Instead, not knowing whether there were changes since the last checkpoint,
>> the system truncated the relation.
>>
>> What use case is there for a behavior that the last checkpoint data is
>> left on the relation upon restarting - not knowing whether it was possible
>> the other data could have been written subsequent?
>>
>
> I would like a way to have unlogged tables be available on a replica
> provided that no changes were made to them between the pg_basebackup and
> the recovery point.
>

> My use case is that I mark certain read-only-after-bulk-loading tables as
> unlogged solely to avoid blowing out the log archive during the loading
> phase and refresh phase.  This is stuff like vendor catalogs, NCBI
> datasets, ChEMBL datasets, etc, which can simply be re-derived from the
> reference.  It would be nice if these were still available (without having
> to repeat the ETL) after crashes provided they were not written to since a
> checkpoint, and available on cloned test servers without having to repeat
> the ETL on those as well.
>
>

​My gut reaction is that those should be in their own clusters and accessed
via postgres_fdw...

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 the slave to basically "rewrite" its existing table by
pointing to the newly supplied version.  Some kind of "CREATE STATIC TABLE"
and "PUSH STATIC TABLE TO {all | replica name}" command combo...though
ideally with less manual intervention...

David J.​

Re: [PERFORM] unlogged tables

[David G. Johnston]

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 periodically flush data to disk and in case of crush
>> postgres to load only the data that existed at last flush? The periodic
>> flush could be configurable, for example every 30 minutes or after x rows
>> updated/inserted.
>>
>
> There is no such facility implemented for UNLOGGED TABLEs. That could be a
> feature request though.
>
>
Well, that is half right anyway.  UNLOGGED tables obey checkpoints just
like any other table.  The missing feature is an option to leaved restored
the last checkpoint.  Instead, not knowing whether there were changes since
the last checkpoint, the system truncated the relation.

What use case is there for a behavior that the last checkpoint data is left
on the relation upon restarting - not knowing whether it was possible the
other data could have been written subsequent?

David J.

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

[David G. Johnston]

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 several tables containing millions of rows and let say 5
> columns. Those five columns, depending of their  combination give me a 6th
> value.
> We have complex patterns to match and using simple LIKE / EQUAL and so on
> wouldn't be enough. This can be applied to N number of table so we
> refactored this process into a function that we can use in the SELECT
> statement, by giving only the 5 values each time.
>
> I wouldn't mind using a table and mapping it through a join  if it were for
> my own use.
> But the final query has to be readable and usable for almost-non-initiated
> SQL user... So using a function with encapsulated case when seemed to be a
> good idea and so far worked nicely.
>
> But we might consider changing it if we have no other choice...
>
> Regards,
>
> Kevin
>
>
​Thoughts...​

​Memoization: http://en.wikipedia.org/wiki/Memoization

Rewrite the function in pl/perl​ and compare performance

​Hierarchy of CASE statements allowing you to reduce the number of
possibilities in exchange for manually pre-processing the batches on a
significantly less complicated condition probably using only 1 or 2 columns
instead of all five.​

​I'm not familiar with the caching constraint or the data so its hard to
make more specific suggestions.

David J.​

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

[David G Johnston]

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 always be a non-null value returned. 
> 
> In both cases though, I rather wonder why you're using LATERAL at all, as
> opposed to just calling the function in the main query when you want its
> result.  The query planner can't be expected to make up for arbitrary
> amounts of stupidity in the formulation of the submitted query.

I'm trying to answer this with a bit more detail but cannot because the OP
provided too little information which is then causing Tom to make
assumptions.  I'm not sure to what degree the ORM is being stupid here since
I do not know why it thinks LATERAL is more appropriate than a select-list
function call for a non-SRF function (which I have to presume this is, but
it is not stated).

With respect to "the function will never return NULL": this is not the
issue.  The issue is that the function could return nothing (i.e., zero
records) in which case the CROSS JOIN would suppress the corresponding
correlated row from the result.

Non-SRF functions are more easily used within the select-list of the query
instead of attached to a LATERAL clause; the only issue there is when the
function returns a composite and you try to immediately explode it into its
constituent parts - the function will be evaluated multiple times.  I'm not
sure if that is what Tom is saying above but the combination of that
limitation and limited optimizations if the function is in LATERAL seems to
be in conflict here.

There has been a recent uptick in interest in making PostgreSQL more ORM
friendly (i.e., more able to simply ignore stuff that is added to the query
even though a particular call doesn't actually need it) but I haven't seen
anyone looking into LATERAL.  More detailed reports may at least bring
exposure to what is being used in the wild and garner interest from other
parties in improving things.  Unfortunately this report is too limited to
really make a dent; lacking even the name of the ORM that is being used and
the entire queries that are being generated - and why.

David J.




--
View this message in context: 
http://postgresql.nabble.com/query-laziness-of-lateral-join-with-function-tp5837706p5837735.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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: Migrating a FoxPro system and would like input on the best way to achieve optimal performance

[David G Johnston]

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 330,000 files and I am not certain if that will cause an issue
> with degraded file system performance.

I suggest you not think about "files" when pondering about PostgreSQL.  That
said, 330,000 tables within a single database, or even cluster, is likely to
be problematic.


> Is it possible to create a tablespace for each group and then create
> partitioned tables for the groups within the group's tablespace to limit
> the number of files in a directory? 

Same point about ignoring "files" and "directories".  Tablespaces let you
place different kinds of data onto different filesystems; using them for
"directory management" is not particularly helpful.

Note that I presume you are planning on leaving the database backend on
Windows...my experience is more with Linux but your core issue is data model
which is largely O/S agnostic.


> I plan on utilizing the built-in streaming replication, so I assume if I
> went the tablespace route I would need to create directories for all
> future groups from the outset since creating them individually with code
> on the backup systems would be difficult.

Which is another reason why tablespaces should not implement logical
attributes of the system.


> Another option would be placing an extra field in each table identifying
> the group it belongs to and combining all of the separate tables of the
> same definition into one table. This would result in some tables having
> 300 million entries currently and that would climb over the next 18
> months.

This is the canonical solution to multi-tenancy.  Physical partitioning then
occurs on a hash of whatever key you are using; you do not have one tenant
per table.


> The final option I can see is creating a schema for each of the different
> clients. I am not certain if this is a better option than partitioned
> tables. I haven't been able to determine if schema objects are stored in a
> sub directory or if they are in the same directory as all of the other
> tables. If they are in the same directory then the same issue arises as
> the partitioned tables.

Depending on whether clients are able to get access to the data directly you
can also consider having a separate database for each client.  I would then
recommend using either dblink or postgres_fdw to connect to the single
shared database - or just replicate the shared schema and data subset into
each individual client database.


> Of course, I am certain there are a number of other possibilities that I
> am overlooking. I am just trying to determine the best way to move this
> over and get things into a more modern system.

Without understanding how your application works and makes use of the
existing data it is difficult to suggest alternatives.  Specifically around
data visibility and the mechanics behind how the application access
different clients' data.

I would personally choose only between having different databases for each
client or using a "client_id" column in conjunction with a multi-tenant
database.  Those are the two logical models; everything else (e.g.
partitioning) are physical implementation details.

David J.



--
View this message in context: 
http://postgresql.nabble.com/Migrating-a-FoxPro-system-and-would-like-input-on-the-best-way-to-achieve-optimal-performance-tp5837211p5837241.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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] Postgres does not use indexes with OR-conditions

[David G Johnston]

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.285022+08'
>>>  and (
>>>fd.creation_time < '2011-11-07 10:39:07.285022+08'
>>>  or (fd.creation_time = '2011-11-07 10:39:07.285022+08' and
>>> fd.financial_document_id < 100)
>>>  )
>>> order by fd.creation_time desc
>>> limit 200
>>
>> Could you not rewrite it as something this?:
>>
>> where fd.creation_time <= '2011-11-07 10:39:07.285022+08'
>> and (fd.creation_time < '2011-11-07 10:39:07.285022+08'
>>   or fd.financial_document_id < 100)
> 
> Yeah, when there are two ways to write a query that are logically
> equivalent, it is better to put the AND at the higher level than
> the OR.  On the other hand, why not simply write it as?:
> 
> select *
>   from commons.financial_documents fd
>   where (fd.creation_time, fd.financial_document_id)
>   < ('2011-11-07 10:39:07.285022+08', 100)
>   order by fd.creation_time desc
>   limit 200

>From personal experience and observation on these lists record inequality is
not particularly intuitive.  I'm also not sure someone is likely to really
"get it" until they have a problem for which the above is the solution.

That said is there a place where we supply solutions and idioms to common
queries?  This query as well as pagination-oriented queries are two that
come to mind.  I think the material would fit well in the tutorial section
but having some kind of quick synopsis and cross reference in the
performance chapter would aid someone whose looking to solve a problem and
not in general education mode.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Postgres-does-not-use-indexes-with-OR-conditions-tp5826027p5826065.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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] IS NOT NULL and LEFT JOIN

[David G Johnston]

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* identical.  Go consult any SQL reference.  Left join
>>> conditions don't work the way you seem to be thinking: after the join,
>>> the RHS column might be null, rather than equal to the LHS column.
>> Yes, I was wrong to assume that c.user_info=u.id because of the LEFT
>> JOIN.
>> 
>> But since I only want rows where u.id IS NOT NULL, in any case I will 
>> also have c.user_info IS NOT NULL.
>> 
>> Also, having a foreign key, if c.user_info is not null, it will have a 
>> match in u. So in that case, either both c.user_info and c.id are null 
>> in the result rows, or they are equal.
> The planner only expends so much effort converting between equivalent
> query forms.  By adding u.id IS NOT NULL you are saying that you really
> meant to use INNER JOIN instead of LEFT JOIN but whether the planner can
> and/or does act on that information in the WHERE clause to modify its
> joins is beyond my knowledge.  It doesn't seem to and probably correctly
> isn't worth adding the planner cycles to fix a poorly written/generated
> query on-the-fly.
> 
> 
> Now that it has been pointed out that the two queries you supplied are
> semantically different it is unclear what your point here is.  It is known
> that Hibernate (and humans too) will generate sub-optimal plans that can
> be rewritten using relational algebra and better optimized for having done
> so.  But such work takes resources that would be expended for every single
> query while manually rewriting the sub-optimal query solves the problem
> once-and-for-all.
> 
> David J.

Didn't sound right what I wrote above...

The presence of the "OR" screws things up even further since it does force
the use of LEFT JOIN mechanics for the single case where the name and e-mail
match.

I would maybe try a UNION DISTINCT query instead of an OR clause if you want
to have a query that performs better than the Hibernate one...otherwise
others more knowledgeable than myself have not made any indication that the
planner is unintentionally deficient in its handling of your original query.

You may try posting your actual question, and not the SQL, and see if that
sparks any suggestions.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/IS-NOT-NULL-and-LEFT-JOIN-tp5823591p5823739.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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] IS NOT NULL and LEFT JOIN

[David G Johnston]

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 thinking: after the join,
>> the RHS column might be null, rather than equal to the LHS column.
> Yes, I was wrong to assume that c.user_info=u.id because of the LEFT JOIN.
> 
> But since I only want rows where u.id IS NOT NULL, in any case I will 
> also have c.user_info IS NOT NULL.
> 
> Also, having a foreign key, if c.user_info is not null, it will have a 
> match in u. So in that case, either both c.user_info and c.id are null 
> in the result rows, or they are equal.

The planner only expends so much effort converting between equivalent query
forms.  By adding u.id IS NOT NULL you are saying that you really meant to
use INNER JOIN instead of LEFT JOIN but whether the planner can and/or does
act on that information in the WHERE clause to modify its joins is beyond my
knowledge.  It doesn't seem to and probably correctly isn't worth adding the
planner cycles to fix a poorly written/generated query on-the-fly.


Now that it has been pointed out that the two queries you supplied are
semantically different it is unclear what your point here is.  It is known
that Hibernate (and humans too) will generate sub-optimal plans that can be
rewritten using relational algebra and better optimized for having done so. 
But such work takes resources that would be expended for every single query
while manually rewriting the sub-optimal query solves the problem
once-and-for-all.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/IS-NOT-NULL-and-LEFT-JOIN-tp5823591p5823737.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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 query

[David G Johnston]

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_state ON t1(name,state);
> 
> -- Create some sample data
> DO $$
> DECLARE
> states text[] := array['UNKNOWN', 'TODO', 'DONE', 'UNKNOWN'];
> BEGIN
> FOR v IN 1..20 LOOP
>   INSERT INTO t1 VALUES('user'||v, states[floor(random()*4)]);
>   INSERT INTO t1 VALUES('user'||v, states[floor(random()*4)]);
>   INSERT INTO t1 VALUES('user'||v, states[floor(random()*4)]);
>   INSERT INTO t1 VALUES('user'||v, states[floor(random()*4)]);
> END LOOP;
> END $$;
> 
> 
> CREATE OR REPLACE FUNCTION state_to_int(state character varying) RETURNS
> integer
> LANGUAGE plpgsql IMMUTABLE STRICT
> AS $$BEGIN
> IF state = 'UNKNOWN' THEN RETURN 0;
> ELSIF state = 'TODO' THEN RETURN 1;
> ELSIF state = 'DONE' THEN RETURN 2;
> ELSIF state = 'NOT REQUIRED' THEN RETURN 3;
> ELSE RAISE EXCEPTION 'state_to_int called with invalid state value';
> END IF;
> END;$$;
> 
> CREATE OR REPLACE FUNCTION int_to_state(state integer) RETURNS character
> varying
> LANGUAGE plpgsql IMMUTABLE STRICT
> AS $$BEGIN
> IF state = 0 THEN RETURN 'UNKNOWN';
> ELSIF state = 1 THEN RETURN 'TODO';
> ELSIF state = 2 THEN RETURN 'DONE';
> ELSIF state = 3 THEN RETURN 'NOT REQUIRED';
> ELSE RAISE EXCEPTION 'int_to_state called with invalid state value';
> END IF;
> END;$$;
> 
> -- Why is this a lot slower
> explain (analyse, buffers) select name,
> int_to_state(min(state_to_int(state))) as status from t1 group by t1.name;
> 
> -- Than this?
> explain (analyze, buffers) select name, (array['UNKNOWN', 'TODO', 'DONE',
> 'NOT REQUIRED'])[min(
> CASE state
> WHEN 'UNKNOWN' THEN 0
> WHEN 'TODO' THEN 1
> WHEN 'DONE' THEN 2
> WHEN 'NOT REQUIRED' THEN 3
> END)] AS status from t1 group by t1.name;
> 
> -- This is also very much slower
> explain (analyze, buffers) select name, (array['UNKNOWN', 'TODO', 'DONE',
> 'NOT REQUIRED'])[min(state_to_int(state))] AS status from t1 group by
> t1.name;
> 
> This was done on:
> PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
> 4.8.2-19ubuntu1) 4.8.2, 64-bit
> 
> We get results like this:
> QUERY PLAN
> 
> ---
>  GroupAggregate  (cost=0.42..280042.62 rows=208120 width=15) (actual
> time=0.076..2439.066 rows=20 loops=1)
>Buffers: shared hit=53146
>->  Index Scan using t1_name on t1  (cost=0.42..21931.42 rows=80
> width=15) (actual time=0.009..229.477 rows=80 loops=1)
>  Buffers: shared hit=53146
>  Total runtime: 2460.860 ms
> (5 rows)
> 
> QUERY PLAN
> 
> ---
>  GroupAggregate  (cost=0.42..36012.62 rows=208120 width=15) (actual
> time=0.017..559.384 rows=20 loops=1)
>Buffers: shared hit=53146
>->  Index Scan using t1_name on t1  (cost=0.42..21931.42 rows=80
> width=15) (actual time=0.008..197.133 rows=80 loops=1)
>  Buffers: shared hit=53146
>  Total runtime: 574.550 ms
> (5 rows)
> 
> QUERY PLAN
> 
> ---
>  GroupAggregate  (cost=0.42..228012.62 rows=208120 width=15) (actual
> time=0.042..2089.367 rows=20 loops=1)
>Buffers: shared hit=53146
>->  Index Scan using t1_name on t1  (cost=0.42..21931.42 rows=80
> width=15) (actual time=0.008..237.854 rows=80 loops=1)
>  Buffers: shared hit=53146
>  Total runtime: 2111.004 ms
> (5 rows)
> 
> 
> We cannot change our table structure to reflect something more sensible.
> What we would really like to know is why using functions is so much slower
> than the unreadable method.
> 
> Regards
> 
> Ross

Pl/pgsql functions are black boxes and expensive to execute; you should
define these functions as SQL functions and see if that helps.

David J.






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Slow-query-tp5820086p5820096.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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] weird execution plan

[David G Johnston]

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 answered I
will theorize.

SELECT count(*) FROM ( SELECT DISTINCT col FROM tbl )

vs

SELECT count(DISTINCT col) FROM tbl

The code for "SELECT DISTINCT col" is likely highly efficient because it
works on complete sets of records.

The code for "SELECT count(DISTINCT col)" is at a relative disadvantage
since it must evaluate one row at a time and remember whether it had seen
the same value previously before deciding whether to increment a counter.

With a large number of duplicate rows the process of making the row set
smaller before counting the end result will perform better since fewer rows
must be evaluated in the less efficient count(DISTINCT) expression - the
time saved there more than offset by the fact that you are effectively
passing over that subset of the data a second time.

HashAggregate(1M rows) + Aggregate(200k rows) < Aggregate(1M rows)

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/weird-execution-plan-tp5818730p5818905.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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] weird execution plan

[David G Johnston]

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.nabble.com/weird-execution-plan-tp5818730p5818733.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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] query performance with hstore vs. non-hstore

[David G Johnston]

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)).value::numeric as
> visits from weekly_hstore a  join seg1 b on a.ref_id=b.ref_id )foo  group
> by cha_type  order by sum(visits) desc;

What version of PostgreSQL are you using?

Two calls to each() and cast to numeric are not free.

Your sequential scan savings is nearly 9 seconds but you lose all of that,
and more, when PostgreSQL evaluates the result of the scan and has to
process the each() and the cast before it performs the join against the
expanded result.  There is no planner node for this activity but it does
cost time - in this case more time than it would take to simply store the
native data types in separate rows.

You really should expand the hstore after the join (i.e., in the top-most
select-list) but in this case since the join removed hardly any rows the
gain from doing so would be minimal.  The idea being you should not expand
the hstore of any row that fails the join condition since it will not end up
in the final result anyway.

Also, in this specific case, the call to each(...).key is pointless - you
never use the data.

If you did need to use both columns, and are using 9.3, you should re-write
this to use LATERAL.

In 9.2- you, possibly using a CTE, could do something like this:

SELECT (each).* FROM (
SELECT each(hs) FROM ( VALUES('k=>1'::hstore) ) h (hs)
) src

This is a single call to each(), in a subquery, which result is then
expanded using (col).* notation in the parent query.  This avoids calling
each twice - and note that (each(...).*) does not work to avoid the
double-call - you have to use a subquery / a CTE one to ensure that it is
not collapsed (offset 0 should work too but I find the CTE one a little
cleaner personally).

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/query-performance-with-hstore-vs-non-hstore-tp5817109p5817281.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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] autocommit (true/false) for more than 1 million records

[David G Johnston]

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]
> > wrote:
>
> > Alex Goncharov <[hidden email]
> > wrote:
>
> > > The whole thing is aborted then, and the good 99 records are not
> > > making it into the target table.
> >
> > Right.  This is one reason people often batch such copies or check
> > the data very closely before copying in.
>
> How do I decide, before starting a COPY data load, whether such a load
> protection ("complexity") makes sense ("is necessary")?
>
>
​You should probably consider something like:

http://pgloader.io/

​(I know there are others, this one apparently has the best marketing
team...)​

Normal case, with normal COPY, you load a bad file​ into an empty table, it
fails, you truncate and get better data for the next attempt.

How long that will take is system (IOPS/CPU) and data dependent.

The probability of failure is source dependent - and prior experience plays
a large role here as well.

If you plan to load directly into a live table the wasted space from a bad
load could kill you so smaller partial loads are better - if you can afford
the implicit system inconsistency such a partial load would cause.

If you understand how the system works you should be able to evaluate the
different pieces and come to a conclusion as how best to proceed in a
specific situation.  No one else on this list has the relevant information
to make that judgement call.  If this is just asking about rules-of-thumb
I'd say figure out how many records 100MB consumes and COMMIT after that
many records.  10,000 records is also a nice round number to pick -
regardless of the amount of MB consumed.  Start there and tweak based upon
experience.

> If you are not taking advantage of the "unlogged load" optimization,
> > you will have written Write Ahead Log (WAL) records, too -- which
> > (depending on your configuration) you may be archiving.  In that
> > case, you may need to be concerned about the archive space required.
>
> "... may need to be concerned ..." if what?  Loading 1 MB? 1 GB? 1 TB?
>
> If I am always concerned, and check something before a COPY, what
> should I be checking?  What are the "OK-to-proceed" criteria?
>
>
​If you only have 500k free in your archive directory that 1MB file will
pose a problem...though if you have 4TB of archive available the 1TB would
fit easily.  Do you compress your WAL files before shipping them off to the
archive?  How compressible is your data?

I'm sure people have decent rules-of-thumb here but in the end your
specific environment and data, especially at the TB scale, is going to be
important; and is something that you will only discover through testing.


>
> > If you have foreign keys defined for the table, you may get into
> > trouble on the RAM used to track pending checks for those
> > constraints.  I would recommend adding any FKs after you are done
> > with the big bulk load.
>
> I am curious about the simplest case where only the data storage is to
> be worried about. (As an aside: the CHECK and NOT NULL constrains are
> not a storage factor, right?)
>
>
Correct


>
> > PostgreSQL does *not* have a "rollback log" which will impose a
> > limit.
>
> Something will though, right?  What would that be? The available disk
> space on a file system? (I would be surprised.)
>
>
> > > Say, I am COPYing 100 TB of data and the bad records are close
> > > to the end of the feed -- how will this all error out?
> >
> > The rows will all be in the table, but not visible to any other
> > transaction.
>
> I see.  How much data can I fit there while doing COPY?  Not 1 TB?
>
> -- Alex
>

​You need the same amount of space that you would require if the file
imported to completion.

​PostgreSQL is optimistic in this regard - it assumes you will commit and
so up until failure there is no difference between a good and bad import.​
 The magic is described in Slide 24 of the MVCC link above (
http://momjian.us/main/writings/pgsql/mvcc.pdf) - if the transaction is
aborted then as far as the system is concerned the written data has been
deleted and can be cleaned up just like if the following sequence of
commands occurred:

BEGIN;
COPY tbl FROM ;
COMMIT; ---success
DELETE FROM tbl ;

​Hence the comment to "TRUNCATE" after a failed load if at all possible -
to avoid the unnecessary VACUUM on tbl...

QUESTION: would the vacuum reclaim the disk space in this situation (I
presume yes) because if not, and another imported was to be attempted,
ideally the allocated space could be reused.

I'm not sure what a reasonable formula would be, especially at the TB
scale, but roughly 2x the size of the imported (uncompressed) file would be
a good starting point (table + WAL).  You likely w

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

[David G Johnston]

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!
> Emi

Typically the larger the n the better.  Locking and risk of data loss on a
failure are the tradeoffs to consider.  Other factors, like memory, make
choosing too large an n bad so using 500,000 is probably wrong but 500 is
probably overly conservative.  Better advice depends on context and
hardware.

You should also consider upgrading to a newer, supported, version of
PostgreSQL.

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/autocommit-true-false-for-more-than-1-million-records-tp5815943p5815946.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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] query on parent partition table has bad performance

[David G Johnston]

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_rank_2014_monthly and it has 7
> partitions inherited from the parent table, each month with one partition. 
> The weird thing is query out of the parent partition is as slow as query
> from a non-partitioned table, however, query from child table directly is
> really fast.
> 
> have no idea... is this an expected behavior of partition table in old
> releases?
> 
> 
> hitwise_uk=# explain analyze select * from test_rank_2014_07 r WHERE
> r.date = 201407 ;
> QUERY PLAN
> --
>  Seq Scan on test_rank_2014_07 r  (cost=0.00..169797.75 rows=7444220
> width=54) (actual time=0.007..1284.622 rows=7444220 loops=1)
>Filter: (date = 201407)
>  Total runtime: 1831.379 ms
> (3 rows)
> 
> -- query on parent table
> hitwise_uk=# explain analyze select * from test_rank_2014_monthly r WHERE
> r.date = 201407 ;
>   QUERY
> PLAN
> --
>  Result  (cost=0.00..169819.88 rows=7444225 width=54) (actual
> time=0.009..4484.552 rows=7444220 loops=1)
>->  Append  (cost=0.00..169819.88 rows=7444225 width=54) (actual
> time=0.008..2495.457 rows=7444220 loops=1)
>  ->  Seq Scan on test_rank_2014_monthly r  (cost=0.00..22.12
> rows=5 width=54) (actual time=0.000..0.000 rows=0 loops=1)
>Filter: (date = 201407)
>  ->  Seq Scan on test_rank_2014_07 r  (cost=0.00..169797.75
> rows=7444220 width=54) (actual time=0.007..1406.600 rows=7444220 loops=1)
>Filter: (date = 201407)
>  Total runtime: 5036.092 ms
> (7 rows)
> 
> --query on non-partitioned table
> hitwise_uk=# explain analyze select * from rank_2014_monthly r WHERE
> r.date = 201407 ;
>   QUERY PLAN
> --
>  Seq Scan on rank_2014_monthly r  (cost=0.00..1042968.85 rows=7424587
> width=54) (actual time=3226.983..4537.974 rows=7444220 loops=1)
>Filter: (date = 201407)
>  Total runtime: 5086.096 ms
> (3 rows)
> 
> 
> check constraints on child table is something like below:
> ...
> Check constraints:
> "test_rank_2014_07_date_check" CHECK (date = 201407)
> Inherits: test_rank_2014_monthly
> 
> Thanks,
> Suya

Given that the 2nd and 3rd queries perform about equal the question is why
the first query performs so much better.  I suspect you are not taking any
care to avoid caching effects and so that it what you are seeing.  Its hard
to know for sure whether you ran the three queries in the order
listed...which if so would likely negate this theory somewhat.

Adding (BUFFERS) to your explain would at least give some visibility into
caching effects - though since that is only available in supported versions
that is not an option for you.  Still, it is the most likely explanation for
what you are seeing.

There is time involved to process the partition constraint exclusion but I'm
doubting it accounts for a full 3 seconds...

David J.






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/query-on-parent-partition-table-has-bad-performance-tp5815523p5815552.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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] two table join with order by on both tables attributes

[David G Johnston]

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.priority desc limit 10;
> 
> Very typical web query.
> 
> No matter which composite indexes i try, postgresql can not make efficient
> nested loop plan using indexes.
> It chooses all sorts of seq scans and hash joins or merge join and always
> a sort node and then a limit 10.
> 
> Neither plan provides acceptable performance. And tables tend to grow =\
> 
> Can anybody suggest something or explain this behavior?

Can you explain why a nested loop is best for your data?  Given my
understanding of an expected "priority"cardinality I would expect your ORDER
BY to be extremely inefficient and not all that compatible with a nested
loop approach.

You can use the various parameters listed on this page to force the desired
plan and then provide EXPLAIN ANALYZE results for the various executed plans
and compare them.

http://www.postgresql.org/docs/9.3/interactive/runtime-config-query.html#RUNTIME-CONFIG-QUERY-ENABLE

And now for the obligatory "read this" link:

https://wiki.postgresql.org/wiki/SlowQueryQuestions

If you can show that in fact the nested loop (or some other plan) performs
better than the one chosen by the planner - and can provide data that the
developers can use to replicate the experiment - then improvements can be
made.  At worse you will come to understand why the planner is right and can
then explore alternative models.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/two-table-join-with-order-by-on-both-tables-attributes-tp5814135p5814137.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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] Query performing very bad and sometimes good

[David G Johnston]

Andreas Joseph Krogh-2 wrote
> Hi all.   Running version: on=> select version();
>    version
>  
> 
>   PostgreSQL 9.3.2 on x86_64-unknown-linux-gnu, compiled by gcc
> (Ubuntu/Linaro 
> 4.6.3-1ubuntu5) 4.6.3, 64-bit     

9.3.2 is not release-worthy


> Bad:
> Index Scan Backward using origo_email_delivery_received_idx on
> origo_email_delivery del (cost=0.42..1102717.48 rows=354038 width=98)
> (actual time=0.017..309196.670 rows=354296 loops=1)
> 
>>>Add 4 new records<<
> 
> Good (-ish):
> Index Scan Backward using origo_email_delivery_received_idx on
> origo_email_delivery del (cost=0.42..1102717.48 rows=354038 width=98)
> (actual time=0.019..2431.773 rows=354300 loops=1)

The plans appear to be basically identical - and the queries/data as well
aside from the addition of 4 more unmatched records.

The difference between the two is likely attributable to system load
variations combined with the effect of caching after running the query the
first (slow) time.

Doing OFFSET/LIMIT pagination can be problematic so I'd be curious what
would happen if you got rid of it.  In this specific case the result set is
only 75 with 101 allowed anyway.

The left joins seem to be marginal so I'd toss those out and optimize the
inner joins and, more likely, the correlated subqueries in the select list. 
You need to avoid nested looping over 300,000+ records somehow - though I'm
not going to be that helpful in the actual how part...

Note that in the inner-most loop the actual time for the cached data is half
of the non-cached data.  While both are quite small (0.002/0.004) the
300,000+ loops do add up.  The same likely applies to the other planning
nodes but I didn't dig that deep.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Query-performing-very-bad-and-sometimes-good-tp5813831p5813847.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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: Slow query with indexed ORDER BY and LIMIT when using OR'd conditions

[David G Johnston]

johno wrote
> Oh, yes I do understand that if I remove the outer limit, the semantics of
> the query would change. However I am looking for the counterexample *with*
> the limit clauses. Maybe I just don't understand what relationally
> equivalent means, sorry about that.
> 
> 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@

>> wrote:
> 
>> 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 LIMIT 100 from both queries...
>>
>> The first query would return a maximal number of rows that meet the OR
>> criteria while the second query would return at most 200 rows since both
>> sub-queries would still have their own independent LIMIT 100 clauses.
>>
>> David J.

Try following my lead and bottom-post, please.

Anyway, the query has no clue that because of the final LIMIT 100 that the
two different feeding queries are just going to happen to end up providing
the same result.  Maybe, in this particular instance, it is theoretically
possible to make such a proof but generally that is not the case and so such
an optimization has not made into the codebase even if it theoretically
could be done (I'm not convinced it could but do not know enough to explain
to someone else why I have that impression).

I do not know enough to answer why this situation is any different from a
similar partitioning scenario.  An example showing exactly what a similar
partitioning query looks like would help in this regard.

If you are looking for considerably more insight into the planner workings
and why it does or doesn't do something you will need to wait for others.  I
can, to a reasonable degree, deconstruct a pair of queries and either
explain or guess as to why things are happening but that is mostly applied
deductive reasoning and not because I have any particular insight into the
codebase.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Slow-query-with-indexed-ORDER-BY-and-LIMIT-when-using-OR-d-conditions-tp5812282p5812291.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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: Slow query with indexed ORDER BY and LIMIT when using OR'd conditions

[David G Johnston]

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 LIMIT 100 from both queries...

The first query would return a maximal number of rows that meet the OR
criteria while the second query would return at most 200 rows since both
sub-queries would still have their own independent LIMIT 100 clauses.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Slow-query-with-indexed-ORDER-BY-and-LIMIT-when-using-OR-d-conditions-tp5812282p5812289.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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: Slow query with indexed ORDER BY and LIMIT when using OR'd conditions

[David G Johnston]

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 did not make an optimization but rather choose to
reformulate the question so that it could be answered more easily while
still providing an acceptable answer.

The question main question is better phrased as:

Give me 100 updated at t(0) but only that are subsequent to a given ID.  If
there are less than 100 such records give me enough additional rows having t
> t(0) so that the total number of rows returned is equal to 100.

Both queries give the same answer but only due to the final LIMIT 100. They
arrive there in different ways which necessitates generating different
plans.  At a basic level it is unable to push down LIMIT into a WHERE clause
and it cannot add additional sub-queries that do not exist in the original
plan - which includes adding a UNION node.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Slow-query-with-indexed-ORDER-BY-and-LIMIT-when-using-OR-d-conditions-tp5812282p5812285.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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] UNION and bad performance

[David G Johnston]

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.00 rows=2000 width=4)
>>>   ->  Append  (cost=0.00..40.00 rows=2000 width=4)
> 
> 
> Why in this case the estimated number of rows is 2000? Is it standard
> planner behavior?

http://www.postgresql.org/docs/9.1/static/sql-createfunction.html

Note the "ROWS" property.

Functions are black-boxes to the planner so it has no means of estimating a
row count.  So a set returning function uses 1,000 and all others use 1.

Determining "COST" is similarly problematic.

David J.







--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/UNION-and-bad-performance-tp3301375p5806450.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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] parse/bind/execute

[David G Johnston]

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 the above metrics mean and if it's a
> problem. I found people can clearly clarify the parse/bind/execute time of
> a query. To my limited knowledge of Postgres, using explain analyze, I can
> only get the total execution time.
> 
> Can someone shed me some light on this subject? How to interpret the
> ratios?
> 
> Thanks,
> Suya

Both are related to using prepared statements (usually with parameters). 
Each bind is a use of an already prepared query with parameters filled in. 
The prepare is the initial preparation of the query.  A ratio of 1 means
that each time you prepare a query you use it once then throw it away. 
Likewise a value of 2 would mean you are executing each prepared statement
twice.

"Usual" statements are those that are not prepared.  The ratio is simply the
counts of each as seen by the database - I do not know specifics as to what
exactly is counted (ddl?).

That low a ratio means that almost all statements you send to the database
are non-prepared.  In those relatively few cases where you do prepare first
you almost always immediately execute a single set of inputs then discard
the prepared statement.

I do not know enough about the underlying data to draw a conclusion but
typically the higher the bind/prepare ratio the more efficient your use of
database resources.  Same goes for the prepare ratio.  The clients you use
and the general usage of the database heavily influence what would be
considered reasonable ratios.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/parse-bind-execute-tp5806132p5806133.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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] group commit

[David G Johnston]

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 presume it
does).

http://www.postgresql.org/docs/9.2/interactive/release-9-2.html
Section: E.9.3.1.1. Performance


> Allow group commit to work effectively under heavy load (Peter Geoghegan,
> Simon Riggs, Heikki Linnakangas)
> 
> Previously, batching of commits became ineffective as the write workload
> increased, because of internal lock contention.

Though based upon your question regarding parallel replication I am thinking
that maybe your concept of "group commit" and the one that was implemented
are quite different...

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/group-commit-tp5806056p5806064.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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 do functions affect query plan?

[David G Johnston]

常超 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_data_pkey PRIMARY KEY (id)
> );
> 
> CREATE INDEX index_measure_data_telegram_id ON measure_data USING btree
> (telegram_id);
> 
> 
> in my scenario,a telegram contains measure data for multiple data items
> and timestamps,
> BTW,another table is for telegram.
> 
> The SQL I used in my application is 
>   select * from measure_data where telegram_id in(1,2,...,n)
> and this query used the index_measure_data_telegram_id index,as expected.
> 
> In order to see the performance of my query ,
> I used the following query to search the measure data for randomly 30
> telegrams.
> 
> 
> explain analyze
> SELECT md.*
>   FROM measure_data md
>   where telegram_id in 
>     (
>  SELECT distinct
>  trunc((132363-66484) * random() + 66484)
>  FROM generate_series(1,30) as s(telegram_id)
>     )
>   ;
> 
> the 132363 and 66484 are the max and min of the telegram id,separately.
> 
> What surprised me is that index is not used,instead,a seq scan is
> performed on measure_data.
> Although,intuitively,in this case,it is much wiser to use the index.
> Would you please give some clue to why this happened?
> 
> "Hash Semi Join  (cost=65.00..539169.32 rows=10277280 width=28) (actual
> time=76.454..17177.054 rows=9360 loops=1)"
> "  Hash Cond: ((md.telegram_id)::double precision = (trunc(((65879::double
> precision * random()) + 66484::double precision"
> "  ->  Seq Scan on measure_data md  (cost=0.00..356682.60 rows=20554560
> width=28) (actual time=0.012..13874.809 rows=20554560 loops=1)"
> "  ->  Hash  (cost=52.50..52.50 rows=1000 width=8) (actual
> time=0.062..0.062 rows=30 loops=1)"
> "    Buckets: 1024  Batches: 1  Memory Usage: 2kB"
> "    ->  HashAggregate  (cost=22.50..42.50 rows=1000 width=0) (actual
> time=0.048..0.053 rows=30 loops=1)"
> "  ->  Function Scan on generate_series s  (cost=0.00..20.00
> rows=1000 width=0) (actual time=0.020..0.034 rows=30 loops=1)"
> "Total runtime: 17177.527 ms"

The planner expects to need to return half the table when you provide 1,000
distinct telegram_ids, which is best handled by scanning the whole table
sequentially and tossing out invalid data.

I am curious if the plan will be different if you added a LIMIT 30 to the
sub-query.

The root of the problem is the planner has no way of knowing whether
generate_series is going to return 1 or 1,000,000 rows so by default it (and
all functions) are assumed (by the planner) to return 1,000 rows.  By adding
an explicit limit you can better inform the planner as to how many rows you
are going to be passing up to the parent query and it will hopefully, with
knowledge of only 30 distinct values, use the index.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-do-functions-affect-query-plan-tp5803993p5803996.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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: recently and selectively slow, but very simple, update query....

[David G Johnston]

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 is
how an update works in MVCC - so doing so explicitly is unlikely to provide
any benefit.  Since that row is continually being inserted, and no other
rows are having this issue, I'm seriously doubting that a dump/restore is
going to have any effect either.  Note that the index scan took twice as
long in the bad case - but still reasonable and you didn't notice any
benefit from a REINDEX.  This is what I would expect.

The only other difference, if concurrency has been ruled out, is the 4 vs 18
buffers that had to be read.  I cannot imagine that, since all 22 were in
cache, that simply reading that much more data would account for the
difference (we're talking a 10,000-fold increase, not 2to4-fold).  The
reason for this particular difference, IIUC, is how may candidate tuples are
present whose visibility has to be accounted for (assuming 1 buffer per
tuple, you needed to scan 4 vs 18 for visibility in the two queries).

Is there any log file information you can share?  Especially if you can set
log_min_statement_duration (or whatever that GUC is named) so that whenever
one of these gets adversely delayed it appears in the log along with
whatever other system messages are being sent.  Checkpoints are a typical
culprit though that should be affecting a great deal more than what you
indicate you are seeing.

I'm pretty certain you are seeing this here largely because of the frequency
of activity on this particular user; not because the data itself is
corrupted.  It could be some kind of symptom of internal concurrency that
you just haven't observed yet but it could also be I/O or other system
contention that you also haven't properly instrumented.  Unfortunately that
is beyond my current help-providing skill-set.

A dump-restore likely would not make anything worse though I'd be surprised
if it were to improve matters.  It also doesn't seem like hardware - unless
the RAM is bad.  Software bugs are unlikely if this had been working well
before 5 days ago.   So, you need to observe the system during both periods
(good and bad) and observe something that is different - probably not within
PostgreSQL if indeed you've minimized concurrency.  And also see if you can
see if any other queries, executed during both these times, exhibit a
performance decrease.  Logging all statements would help matters greatly if
you can afford it in your production environment - it would make looking for
internal concurrency much easier.

David J.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Re-recently-and-selectively-slow-but-very-simple-update-query-tp5802553p5802579.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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: recently and selectively slow, but very simple, update query....

[David G Johnston]

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 generates
significant transaction/query volume (implied by the fact the user has the
most balance updates).

During slow-update executions you want to look at:
pg_stat_activity
pg_locks 

to see what other concurrent activity is taking place.

It is doubtful that dump/restore would have any effect given that the
symptoms are sporadic and we are only talking about a select statement that
returns a single row; and an update that does not hit any indexed column and
therefore benefits from "HOT" optimization.

HTH

David J.






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Re-recently-and-selectively-slow-but-very-simple-update-query-tp5802553p5802555.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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: PostgreSQL's query planner is using the wrong index, what can I do to improve this situation?

[David G Johnston]

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, basically every trade happening on a
> stock every day.
> 
> However I have two similar queries that use completely different index,
> which has HUGE impact on performance.
> 
> *** QUERY START 
> myfinance=> EXPLAIN (buffers,analyze) 
> SELECT COUNT(1) 
> FROM stock_trade st 
> WHERE st.stock_id = any(array(
> SELECT s.id FROM stock s WHERE s.exchange_id IN(1,2,3))
> ) 
> AND st.time BETWEEN '2014-04-22 00:00' AND '2014-04-22 23:59'; 
>   
>  
> QUERY PLAN
>  
> ---
>  Aggregate  (cost=19148.27..19148.37 rows=1 width=0) (actual
> time=3644.474..3644.475 rows=1 loops=1)
>Buffers: shared hit=5994 read=1524
>InitPlan 1 (returns $0)
>  ->  Index Scan using stock_exchange_idx on stock s 
> (cost=28.38..794.17 rows=1482 width=8) (actual time=0.066..4.412 rows=1486
> loops=1)
>Index Cond: (exchange_id = ANY ('{1,2,3}'::bigint[]))
>Buffers: shared hit=34
>->  Index Only Scan using stock_trade_stock_id_time_idx on stock_trade
> st  (cost=58.50..14380.10 rows=15896 width=0) (actual time=8.033..3071.828
> rows=395019 loops=1)
>  Index Cond: ((stock_id = ANY ($0)) AND ("time" >= '2014-04-22
> 00:00:00'::timestamp without time zone) AND ("time" <= '2014-04-22
> 23:59:00'::timestamp without time zone))
>  Heap Fetches: 0
>  Buffers: shared hit=5994 read=1524
>  Total runtime: 3644.604 ms
> *** QUERY END 
> 
> This query is using the 'stock_trade_stock_id_time_idx' multi-column
> index, with good performance.
> However once I change the date to a more recent one, it is suddenly using
> another and MUCH slower index...
> 
> *** QUERY START 
> myfinance=> EXPLAIN (buffers,analyze) 
> SELECT COUNT(1) 
> FROM stock_trade st 
> WHERE st.stock_id = any(array(
> SELECT s.id FROM stock s WHERE s.exchange_id IN(1,2,3))
> ) 
> AND st.time BETWEEN '2014-05-02 00:00' AND '2014-05-02 23:59';  
>   
> QUERY PLAN
> 
> -
>  Aggregate  (cost=859.78..859.88 rows=1 width=0) (actual
> time=115505.403..115505.405 rows=1 loops=1)
>Buffers: shared hit=4433244
>InitPlan 1 (returns $0)
>  ->  Index Scan using stock_exchange_idx on stock s 
> (cost=28.38..794.17 rows=1482 width=8) (actual time=0.047..4.361 rows=1486
> loops=1)
>Index Cond: (exchange_id = ANY ('{1,2,3}'::bigint[]))
>Buffers: shared hit=34
>->  Index Scan using stock_trade_time_idx on stock_trade st 
> (cost=57.50..65.35 rows=1 width=0) (actual time=7.415..114921.242
> rows=395834 loops=1)
>  Index Cond: (("time" >= '2014-05-02 00:00:00'::timestamp without
> time zone) AND ("time" <= '2014-05-02 23:59:00'::timestamp without time
> zone))
>  Filter: (stock_id = ANY ($0))
>  Rows Removed by Filter: 6903136
>  Buffers: shared hit=4433244
>  Total runtime: 115505.545 ms
> *** QUERY END 
> 
> As you see, now it is using the 'stock_trade_time_idx' index.
> I have a similar problem when using IN or EXISTS for stock_id's, it will
> automatically chose the wrong index. But when I tried with
> any(array($subquery)), the right index would be chosen for data that is a
> few days old(Not sure why the query planner is behaving like this).
> 
> I've tried running VACUUM and ANALYZE without any effect. Are there other
> things I can do?

I suspect the the index-only aspect of the first plan is what is giving the
largest performance boost. As time passes the likelihood of having data be
all-visible increases.  I do not know how or if it is possible to force
visibility for this purpose.

Time is likely more selective than stock_id so for the multiple-column index
time should probably the first listed field. 

The planner figures being more selective and filtering is going to be faster
than scanning the much larger section of index covered by the stock_id(s)
and then going and fetching those pages and then checking them for
visibility.  But if it can get most or all of the data directly from the
index then the savings are substantial enough to use the compound index. 
Othe

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

[David G Johnston]

>
>
> 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 = reader_ids || 1 WHERE
> messageid = ..."  I'm not that familiar with how well indexes over arrays
> work or which kind is needed (i.e. gin/gist).
>
>
>
> "is_read" is one of many properties being tracked for a message...
>
>
​But you don't have to have all of them on the same table.  Once you've
identified the messages in question performing a standard join onto a
supplemental detail table should be fairly straight-forward.

Do these other properties have values when "is_read" is false or only when
"is_read" is true?  Since you already allow for the possibility of a
missing record (giving it the meaning of "not read")​ these other
properties cannot currently exist in that situation.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Optimize-query-for-listing-un-read-messages-tp5802097p5802174.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

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

[David G Johnston]

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 dimension does not grow
unbounded?

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 = reader_ids || 1 WHERE
messageid = ..."  I'm not that familiar with how well indexes over arrays
work or which kind is needed (i.e. gin/gist).

HTH

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Optimize-query-for-listing-un-read-messages-tp5802097p5802170.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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] Optimize query for listing un-read messages

[David G Johnston]

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.nabble.com/Optimize-query-for-listing-un-read-messages-tp5802097p5802157.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
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] Best practice question

[David G Johnston]

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 modify_date column
> to current_timestamp when the table is updated? I also have slony in
> place,
> so the trigger will need to be on master and slave. Slony will take care
> of
> suppressing it on the slave and enabling in the event of a switchover, but
> it is additional overhead and validation to make sure nothing failed on
> switchover.
> 
> So considering that we have slony, is it better to use application code to
> update the modify_date or use a trigger? Is a trigger essentially 2
> updates
> to the table? Are there any other risks in using the trigger?
> 
> Thanks
> 
> Tory Blue

Not sure about the Slony trade-off but a before trigger will intercept
before any physical writes and so appears to be a single action.  I would
generally use a trigger so that you know the updared value is recorded (and
can readily add logic for the no-changes situation - if desired).  Not all
table activity has to be initiated by "the application" and since forgetting
to do so is not going to result in any kind of error the probability of the
field becoming useless is non-zero.

It will be slower than doing it native but whether or not that is
significant enough to discard the advantages of triggers is something only
you can decide - ideally after testing.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Best-practice-question-tp5801010p5801011.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance