date:20170825

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. To do 
>> that, we created the following constraints like this example:
>> CHECK (to_char(impression_time, 'MMDD'::text) = '20170202'::text)
>> 
>> 
>> The problem we’re facing is no matter how we’re trying to select from it, it 
>> scans through every partitions.
>
>
>> It scans through every partitions. Shouldn’t it only scan the 
>> dfp_in_network_impressions.dfp_in_network_impressions_20170202 child table? 
>> Or we missing something?
>> Any advice/help would highly appreciated.
>
>https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html#DDL-PARTITIONING-CAVEATS
>|The following caveats apply to constraint exclusion:
>|Constraint exclusion only works when the query's WHERE clause contains
>|constants (or externally supplied parameters). For example, a comparison
>|against a non-immutable function such as CURRENT_TIMESTAMP cannot be
>|optimized, since the planner cannot know which partition the function 
>value
>|might fall into at run time.
>
>...

-- 
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] 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_char(impression_time, 'MMDD'::text) = '20170202'::text)
> 
> 
> The problem we’re facing is no matter how we’re trying to select from it, it 
> scans through every partitions.

> It scans through every partitions. Shouldn’t it only scan the 
> dfp_in_network_impressions.dfp_in_network_impressions_20170202 child table? 
> Or we missing something?
> Any advice/help would highly appreciated.

https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html#DDL-PARTITIONING-CAVEATS
|The following caveats apply to constraint exclusion:
|Constraint exclusion only works when the query's WHERE clause contains
|constants (or externally supplied parameters). For example, a comparison
|against a non-immutable function such as CURRENT_TIMESTAMP cannot be
|optimized, since the planner cannot know which partition the function value
|might fall into at run time.

...

-- 
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] 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 the slower.  strcoll() is pretty darn expensive
> compared to strcmp() :-(

strcoll() is very noticeably slower on macOS, too.

-- 
Peter Geoghegan


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

[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 problem we’re facing is no matter how we’re trying to select from it, it 
scans through every partitions.

Parent table:
 Table "public.dfp_in_network_impressions"
 Column  |Type | Modifiers 
-+-+---
 impression_time | timestamp without time zone | 
 nexus_id| character varying   | 
 line_item_id| bigint  | 
 creative_id | bigint  | 
 ad_unit_id  | bigint  | 
Triggers:
insert_dfp_in_network_impressions_trigger BEFORE INSERT ON 
dfp_in_network_impressions FOR EACH ROW EXECUTE PROCEDURE 
dfp_in_network_impressions_insert_function()
Number of child tables: 214 (Use \d+ to list them.)



One example of the child tables:
Table "dfp_in_network_impressions.dfp_in_network_impressions_20170202"
 Column  |Type | Modifiers 
-+-+---
 impression_time | timestamp without time zone | 
 nexus_id| character varying   | 
 line_item_id| bigint  | 
 creative_id | bigint  | 
 ad_unit_id  | bigint  | 
Indexes:
"idx_dfp_in_network_impressions_20170202_creative_id" btree (creative_id)
"idx_dfp_in_network_impressions_20170202_line_item_id" btree (line_item_id)
Check constraints:
"dfp_in_network_impressions_20170202_impression_time_check" CHECK 
(to_char(impression_time, 'MMDD'::text) = '20170202'::text)
Inherits: dfp_in_network_impressions



Confirmed that the records are in the correct partitions.

We even tried to query with the exact same condition as it is defined in the 
check constraint:
explain select * from dfp_in_network_impressions where to_char(impression_time, 
'MMDD'::text) = '20170202'::text;
QUERY PLAN  
   
---
 Append  (cost=0.00..18655467.21 rows=3831328 width=45)
   ->  Seq Scan on dfp_in_network_impressions  (cost=0.00..0.00 rows=1 width=64)
 Filter: (to_char(impression_time, 'MMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170101  (cost=0.00..7261.48 
rows=1491 width=45)
 Filter: (to_char(impression_time, 'MMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170219  (cost=0.00..20824.01 
rows=4277 width=45)
 Filter: (to_char(impression_time, 'MMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170102  (cost=0.00..28899.83 
rows=5935 width=45)
 Filter: (to_char(impression_time, 'MMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170220  (cost=0.00..95576.80 
rows=19629 width=45)
 Filter: (to_char(impression_time, 'MMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170103  (cost=0.00..88588.22 
rows=18194 width=45)
 Filter: (to_char(impression_time, 'MMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170221  (cost=0.00..116203.54 
rows=23865 width=45)
 Filter: (to_char(impression_time, 'MMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170410  (cost=0.00..158102.98 
rows=32470 width=45)
 Filter: (to_char(impression_time, 'MMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170531  (cost=0.00..116373.83 
rows=23900 width=45)
 Filter: (to_char(impression_time, 'MMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170104  (cost=0.00..91502.48 
rows=18792 width=45)
 Filter: (to_char(impression_time, 'MMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170222  (cost=0.00..106469.76 
rows=21866 width=45)
 Filter: (to_char(impression_time, 'MMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170411  (cost=0.00..152244.92 
rows=31267 width=45)
 Filter: (to_char(impression_time, 'MMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170601  (cost=0.00..117742.66 
rows=24181 width=45)
 Filter: (to_char(impression_time, 'MMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170105  (cost=0.00..87029.80 
rows=17874 width=45)
 Filter: (to_char(impression_time, 'MMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170223

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 ...)
(actual time=0.839..74391.087 ...)

vs. this:

->  Bitmap Heap Scan on lineitem
(cost=336295.10..1970056.39 ...)
(actual time=419620.817..509685.421 ...)
->  Bitmap Index Scan on idx_l_shipmodelineitem000
(cost=0.00..336227.49 ...)
(actual time=419437.172..419437.172 ...)

All the nodes are the same and perform about the same in both cases, so
you can ignore them. This difference it the the root cause you need to
investigate.

The question is why is the sequential scan so much faster than bitmap
index scan? Ideally, the bitmap heap scan should scan the index (in a
mostly sequential way), build a bitmap, and then read just the matching
part of the table (sequentially, by skipping some of the pages).

Now, there are a few reasons why this might not work that well.

Perhaps the table fits into RAM, but table + index does not. That would
make the sequential scan much faster than the index path. Not sure if
this is the case, as you haven't mentioned which TPC-H scale are you
testing, but you only have 4GB of RAM which if fairly low.

Another bit is prefetching - with sequential scans, the OS is able to
prefetch the next bit of data automatically (read-ahead). With bitmap
index scans that's not the case, producing a lot of individual
synchronous I/O requests. See if increasing effective_cache_size (from
default 1 to 16 or 32) helps.

Try generating the plans with EXPLAIN (ANALYZE, BUFFERS), that should
tell us more about how many blocks are found in shared buffers, etc.

regards

-- 
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] 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.e. 
> Machine B is ~10x faster at sorting than Machine B (for this particular 
> query).

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 the slower.  strcoll() is pretty darn expensive
compared to strcmp() :-(

regards, tom lane

-- 
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] 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_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.

[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 2016, 2.6 GHz Intel Core i7 (Skylake), 6 MB L3 
Cache,16 GB 2133 MHz LPDDR3 [2]

Query Performance on Machine A: [3]

CTE Scan on zulu  (cost=40673.620..40742.300 rows=3434 width=56) (actual 
time=6339.404..6339.462 rows=58 loops=1)
  CTE zulu
  ->  HashAggregate  (cost=40639.280..40673.620 rows=3434 width=31) (actual 
time=6339.400..6339.434 rows=58 loops=1)
  Group Key: mike.two, mike.golf
->  Unique  (cost=37656.690..40038.310 rows=34341 width=64) (actual 
time=5937.934..6143.161 rows=298104 loops=1)
  ->  Sort  (cost=37656.690..38450.560 rows=317549 width=64) 
(actual time=5937.933..6031.925 rows=316982 loops=1)
  Sort Key: mike.two, mike.lima, mike.echo DESC, 
mike.quebec
  Sort Method: quicksort  Memory: 56834kB
->  Seq Scan on mike  (cost=0.000..8638.080 rows=317549 
width=64) (actual time=0.019..142.831 rows=316982 loops=1)
Filter: (golf five NOT NULL)
Rows Removed by Filter: 26426

Query Performance on Machine B: [4]

CTE Scan on zulu  (cost=40621.420..40690.100 rows=3434 width=56) (actual 
time=853.436..853.472 rows=58 loops=1)
  CTE zulu
  ->  HashAggregate  (cost=40587.080..40621.420 rows=3434 width=31) (actual 
time=853.433..853.448 rows=58 loops=1)
  Group Key: mike.two, mike.golf
->  Unique  (cost=37608.180..39986.110 rows=34341 width=64) (actual 
time=634.412..761.678 rows=298104 loops=1)
  ->  Sort  (cost=37608.180..38400.830 rows=317057 width=64) 
(actual time=634.411..694.719 rows=316982 loops=1)
  Sort Key: mike.two, mike.lima, mike.echo DESC, 
mike.quebec
  Sort Method: quicksort  Memory: 56834kB
->  Seq Scan on mike  (cost=0.000..8638.080 rows=317057 
width=64) (actual time=0.047..85.534 rows=316982 loops=1)
Filter: (golf five NOT NULL)
Rows Removed by Filter: 26426

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.e. 
Machine B is ~10x faster at sorting than Machine B (for this particular query).

My question is: Why?

I understand that this is a 3rd gen CPU vs a 6th gen, and that things have 
gotten faster despite stagnant clock speeds, but seeing a 10x difference still 
caught me off guard.

Does anybody have some pointers to understand where those gains are coming 
from? Is it the CPU, memory, or both? And in particular, why does Sort benefit 
so massively from the advancement here (~10x), but Seq Scan, Unique and 
HashAggregate don't benefit as much (~2x)?

As you can probably tell, my hardware knowledge is very superficial, so I 
apologize if this is a stupid question. But I'd genuinely like to improve my 
understanding and intuition about these things.

Cheers
Felix Geisendörfer

[1] 
http://www.everymac.com/systems/apple/macbook_pro/specs/macbook-pro-core-i7-2.7-15-mid-2012-retina-display-specs.html
[2] 
http://www.everymac.com/systems/apple/macbook_pro/specs/macbook-pro-core-i7-2.6-15-late-2016-retina-display-touch-bar-specs.html
[3] https://explain.depesz.com/s/hmn
[4] https://explain.depesz.com/s/zVe

-- 
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] 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 that happened in the same situation. The server
> parameters have been set with PGTUNE. I use postgresql version 9.6.4
> on Debian 8 OS with 4 GB memory.
>
> Query|Index(yes/no) |Time Spend|Cost Total
> ===
> 12   Yes   00:08:58  2710805.51
> 12No00:01:42 3365996.34
>
>
> -   Explain Analyze  Query 12  WITH INDEX
> 
> Sort  (cost=2710805.51..2710805.51 rows=1 width=27) (actual
> time=537713.672..537713.672 rows=2 loops=1)
>   Sort Key: lineitem.l_shipmode
> Sort Method:  quicksort  Memory: 25kB
>   ->  HashAggregate  (cost=2710805.47..2710805.50 rows=1 width=27)
> (actual time=537713.597..537713.598 rows=2 loops=1)
>   ->  Merge Join  (cost=1994471.69..2708777.28 rows=270426
> width=27) (actual time=510717.977..536818.802 rows=311208 loops=1)
>   Merge Cond: (orders.o_orderkey = lineitem.l_orderkey)
> ->  Index Scan using orders_pkey on orders
> (cost=0.00..672772.57 rows=1545 width=20) (actual
> time=0.019..20898.325 rows=1472 loops=1)
>   ->  Sort  (cost=1994455.40..1995131.47
> rows=270426 width=19) (actual time=510690.114..510915.678 rows=311208
> loops=1)
>  Sort Key: lineitem.l_orderkey
> Sort Method:  external sort  Disk: 11568kB
>  ->  Bitmap Heap Scan on
> lineitem  (cost=336295.10..1970056.39 rows=270426 width=19) (actual
> time=419620.817..509685.421 rows=311208 loops=1)
>Recheck Cond:
> (l_shipmode = ANY (_{TRUCK,AIR}_::bpchar[]))
> Filter:
> ((l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND
> (l_receiptdate >= _1997-01-01_::date) AND (l_receiptdate < _1998-01-01
> 00:00:00_::timestamp without time zone))
> ->  Bitmap
> Index Scan on idx_l_shipmodelineitem000  (cost=0.00..336227.49
> rows=15942635 width=0) (actual time=419437.172..419437.172
> rows=17133713 loops=1)
>   Index
> Cond: (l_shipmode = ANY (_{TRUCK,AIR}_::bpchar[]))
>
> Total runtime: 537728.848 ms
>
>
> -  Explain Analyze Query 12  WITHOUT INDEX
> 
> Sort  (cost=3365996.33..3365996.34 rows=1 width=27) (actual
> time=101850.883..101850.884 rows=2 loops=1)
>   Sort Key: lineitem.l_shipmode  Sort Method:  quicksort  Memory: 25kB
> ->  HashAggregate  (cost=3365996.30..3365996.32 rows=1 width=27)
> (actual time=101850.798..101850.800 rows=2 loops=1)
> ->  Merge Join  (cost=2649608.28..3363936.68 rows=274616
> width=27) (actual time=75497.181..100938.830 rows=311208 loops=1)
>  Merge Cond: (orders.o_orderkey = lineitem.l_orderkey)
>  ->  Index Scan using orders_pkey on orders
> (cost=0.00..672771.90 rows=1500 width=20) (actual
> time=0.020..20272.828 rows=1472 loops=1)
>   ->  Sort  (cost=2649545.68..2650232.22
> rows=274616 width=19) (actual time=75364.450..75618.772 rows=311208
> loops=1)
> Sort Key: lineitem.l_orderkey
> Sort Method:  external sort
> Disk: 11568kB
>->  Seq Scan on lineitem
> (cost=0.00..2624738.17 rows=274616 width=19) (actual
> time=0.839..74391.087 rows=311208 loops=1)
>  Filter: ((l_shipmode
> = ANY (_{TRUCK,AIR}_::bpchar[])) AND (l_commitdate < l_receiptdate)
> AND (l_shipdate < l_commitdate) AND (l_receiptdate >=
> _1997-01-01_::date) AND (l_receiptdate < _1998-01-01
> 00:00:00_::timestamp without time zone))
>Total runtime:
> 101865.253 ms
>
>  -=-- SQL query 12 --
>   select
> l_shipmode,
> sum(case
> when o_orderpriority = '1-URGENT'
> or o_orderpriority = '2-HIGH'
> then 1
> else 0
> end) as high_line_count,
> sum(case
> when o_orderpriority <> '1-URGENT'
> and o_orderpriority <> '2-HIGH'
> then 1
> else 0
> end) as low_line_count
> from
> orders,
> lineitem
> where
> o_orderkey = l_orderkey
> and l_shipmode in ('TRUCK', 'AIR')
> and l_commitdate < l_receiptdate
> and l_shipdate < l_commitdate
> and l_receiptdate >= date '1997-01-01'

[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
parameters have been set with PGTUNE. I use postgresql version 9.6.4
on Debian 8 OS with 4 GB memory.

Query|Index(yes/no) |Time Spend|Cost Total
===
12   Yes   00:08:58  2710805.51
12No00:01:42 3365996.34


-   Explain Analyze  Query 12  WITH INDEX

Sort  (cost=2710805.51..2710805.51 rows=1 width=27) (actual
time=537713.672..537713.672 rows=2 loops=1)
  Sort Key: lineitem.l_shipmode
Sort Method:  quicksort  Memory: 25kB
  ->  HashAggregate  (cost=2710805.47..2710805.50 rows=1 width=27)
(actual time=537713.597..537713.598 rows=2 loops=1)
  ->  Merge Join  (cost=1994471.69..2708777.28 rows=270426
width=27) (actual time=510717.977..536818.802 rows=311208 loops=1)
  Merge Cond: (orders.o_orderkey = lineitem.l_orderkey)
->  Index Scan using orders_pkey on orders
(cost=0.00..672772.57 rows=1545 width=20) (actual
time=0.019..20898.325 rows=1472 loops=1)
  ->  Sort  (cost=1994455.40..1995131.47
rows=270426 width=19) (actual time=510690.114..510915.678 rows=311208
loops=1)
 Sort Key: lineitem.l_orderkey
Sort Method:  external sort  Disk: 11568kB
 ->  Bitmap Heap Scan on
lineitem  (cost=336295.10..1970056.39 rows=270426 width=19) (actual
time=419620.817..509685.421 rows=311208 loops=1)
   Recheck Cond:
(l_shipmode = ANY (_{TRUCK,AIR}_::bpchar[]))
Filter:
((l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND
(l_receiptdate >= _1997-01-01_::date) AND (l_receiptdate < _1998-01-01
00:00:00_::timestamp without time zone))
->  Bitmap
Index Scan on idx_l_shipmodelineitem000  (cost=0.00..336227.49
rows=15942635 width=0) (actual time=419437.172..419437.172
rows=17133713 loops=1)
  Index
Cond: (l_shipmode = ANY (_{TRUCK,AIR}_::bpchar[]))

Total runtime: 537728.848 ms


-  Explain Analyze Query 12  WITHOUT INDEX

Sort  (cost=3365996.33..3365996.34 rows=1 width=27) (actual
time=101850.883..101850.884 rows=2 loops=1)
  Sort Key: lineitem.l_shipmode  Sort Method:  quicksort  Memory: 25kB
->  HashAggregate  (cost=3365996.30..3365996.32 rows=1 width=27)
(actual time=101850.798..101850.800 rows=2 loops=1)
->  Merge Join  (cost=2649608.28..3363936.68 rows=274616
width=27) (actual time=75497.181..100938.830 rows=311208 loops=1)
 Merge Cond: (orders.o_orderkey = lineitem.l_orderkey)
 ->  Index Scan using orders_pkey on orders
(cost=0.00..672771.90 rows=1500 width=20) (actual
time=0.020..20272.828 rows=1472 loops=1)
  ->  Sort  (cost=2649545.68..2650232.22
rows=274616 width=19) (actual time=75364.450..75618.772 rows=311208
loops=1)
Sort Key: lineitem.l_orderkey
Sort Method:  external sort
Disk: 11568kB
   ->  Seq Scan on lineitem
(cost=0.00..2624738.17 rows=274616 width=19) (actual
time=0.839..74391.087 rows=311208 loops=1)
 Filter: ((l_shipmode
= ANY (_{TRUCK,AIR}_::bpchar[])) AND (l_commitdate < l_receiptdate)
AND (l_shipdate < l_commitdate) AND (l_receiptdate >=
_1997-01-01_::date) AND (l_receiptdate < _1998-01-01
00:00:00_::timestamp without time zone))
   Total runtime:
101865.253 ms

 -=-- SQL query 12 --
  select
l_shipmode,
sum(case
when o_orderpriority = '1-URGENT'
or o_orderpriority = '2-HIGH'
then 1
else 0
end) as high_line_count,
sum(case
when o_orderpriority <> '1-URGENT'
and o_orderpriority <> '2-HIGH'
then 1
else 0
end) as low_line_count
from
orders,
lineitem
where
o_orderkey = l_orderkey
and l_shipmode in ('TRUCK', 'AIR')
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and l_receiptdate >= date '1997-01-01'
and l_receiptdate < date '1997-01-01' + interval '1' year
group by
l_shipmode
order by
l_shipmode


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

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,
   b.nap_discount_num,
  b.nap_makat_cd,
   nap_act_start_dt,
b.nap_debt_line,
   nap_act_end_dt,
   b.row_added_dttm
   b.row_lastmant_dttm,
FROM   ps_rf_inst_prod a,
ANDa.setid  || ''= 'SHARE'
  nap_ip_discount b
WHERE  nap_crm_status  = 'C_04'
ANDb.nap_makat_cd IN (SELECT
term_codeANDb.setid  || ''=
'SHARE'
ANDa.inst_prod_id =



On my screen the order of the lines in the query seem to get messed up,
I'm not sure if that's my email program or a copy/paste error.

From what I can see, you are using subselects in an IN statement,
which can be a problem if that has to be re-evaluated a lot.

It's hard for me to say more because I can't tell what the actual query 
is at the moment.


Regards, Vincent.


--
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] Hi

2017-08-25 Thread Daulat Ram

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


Regards,
Daulat



DISCLAIMER:

This email message is for the sole use of the intended recipient(s) and may 
contain confidential and privileged information. Any unauthorized review, use, 
disclosure or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply email and destroy all copies of 
the original message. Check all attachments for viruses before opening them. 
All views or opinions presented in this e-mail are those of the author and may 
not reflect the opinion of Cyient or those of our affiliates.

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

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

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

[PERFORM] Partitioned table - scans through every partitions

Re: [PERFORM] Execution plan analysis

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

Re: [PERFORM] Hi

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

Re: [PERFORM] Execution plan analysis

[PERFORM] Execution plan analysis

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

Re: [PERFORM] Hi

12 matches

Site Navigation

Mail list logo

Footer information