Re: [PERFORM] Partitioned table - scans through every partitions

2017-08-25 Thread Aniko Belim
Thank you, Justin! Aniko On 8/25/17, 10:44 AM, "Justin Pryzby" wrote: >On Fri, Aug 25, 2017 at 03:36:29PM +, Aniko Belim wrote: >> Hi, >> >> We have an issue with one of our partitioned tables. It has a column with >> timestamp without time zone type, and we had to partition it daily

Re: [PERFORM] Partitioned table - scans through every partitions

2017-08-25 Thread Justin Pryzby
On Fri, Aug 25, 2017 at 03:36:29PM +, Aniko Belim wrote: > Hi, > > We have an issue with one of our partitioned tables. It has a column with > timestamp without time zone type, and we had to partition it daily. To do > that, we created the following constraints like this example: > CHECK (to

Re: [PERFORM] 10x faster sort performance on Skylake CPU vs Ivy Bridge

2017-08-25 Thread Peter Geoghegan
On Fri, Aug 25, 2017 at 8:07 AM, Tom Lane wrote: > I doubt this is a hardware issue, it's more likely that you're comparing > apples and oranges. The first theory that springs to mind is that the > sort keys are strings and you're using C locale on the faster machine but > some non-C locale on th

[PERFORM] Partitioned table - scans through every partitions

2017-08-25 Thread Aniko Belim
Hi, We have an issue with one of our partitioned tables. It has a column with timestamp without time zone type, and we had to partition it daily. To do that, we created the following constraints like this example: CHECK (to_char(impression_time, 'MMDD'::text) = '20170202'::text) The proble

Re: [PERFORM] Execution plan analysis

2017-08-25 Thread Tomas Vondra
Hi, So looking at the plans, essentially the only part that is different is the scan node at the very bottom - in one case it's a sequential scan, in the other case (the slow one) it's the bitmap index scan. Essentially it's this: -> Seq Scan on lineitem (cost=0.00..2624738.17 ...)

Re: [PERFORM] 10x faster sort performance on Skylake CPU vs Ivy Bridge

2017-08-25 Thread Tom Lane
=?utf-8?Q?Felix_Geisend=C3=B6rfer?= writes: > I recently came across a performance difference between two machines that > surprised me: > ... > As you can see, Machine A spends 5889ms on the Sort Node vs 609ms on Machine > B when looking at the "Exclusive" time with explain.depesz.com [3][4]. I.

Re: [PERFORM] Hi

2017-08-25 Thread 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_d

[PERFORM] 10x faster sort performance on Skylake CPU vs Ivy Bridge

2017-08-25 Thread Felix Geisendörfer
Hi, I recently came across a performance difference between two machines that surprised me: Postgres Version / OS on both machines: v9.6.3 / MacOS 10.12.5 Machine A: MacBook Pro Mid 2012, 2.7 GHz Intel Core i7 (Ivy Bridge), 8 MB L3 Cache, 16 GB 1600 MHz DDR3 [1] Machine B: MacBook Pro Late 201

Re: [PERFORM] Execution plan analysis

2017-08-25 Thread Neto pr
2017-08-25 5:31 GMT-03:00 Neto pr : > Dear all > > Someone help me analyze the execution plans below, is the query 12 of > TPC-H benchmark [1]. > I need to find out why the query without index runs faster (7 times) > than with index, although the costs are smaller (see table). > I have other cases

[PERFORM] Execution plan analysis

2017-08-25 Thread Neto pr
Dear all Someone help me analyze the execution plans below, is the query 12 of TPC-H benchmark [1]. I need to find out why the query without index runs faster (7 times) than with index, although the costs are smaller (see table). I have other cases that happened in the same situation. The server

Re: [PERFORM] query runs for more than 24 hours!

2017-08-25 Thread vinny
On 2017-08-22 16:23, Mariel Cherkassky wrote: SELECT a.inst_prod_id, product_id, nap_area2, nap_phone_num, nap_product_id,