Hi, Sorry. I am posting the query details below. Query: SELECT DISTINCT events_rpt_v3.init_service_comp FROM public.events_rpt_v3 events_rpt_v3; events_rpt_v3 is a view based on partition tables. Number of rows in events_rpt_v3: 57878 vmstat o/p: procs -----------memory---------- ---swap-- -----io---- -system-- -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id wa st 1 0 632108 377948 132928 1392996 1 1 10 33 2 2 1 0 99 0 0 1 0 632108 371476 132928 1399016 0 0 3072 36 834 788 49 1 50 0 0 1 0 632108 364408 132928 1405112 0 0 3072 0 795 707 49 1 50 0 0 1 0 632108 357596 132928 1411944 0 0 3584 0 797 712 50 1 50 0 0 1 0 632108 351148 132928 1418540 0 0 3072 0 827 768 49 1 50 0 0 1 0 632108 344700 132928 1425028 0 0 3072 0 811 725 66 1 34 0 0 1 0 632108 337688 132928 1431976 0 0 3584 16 829 802 49 1 50 0 0 1 0 632108 330860 132928 1438808 0 0 3072 0 804 755 50 1 50 0 0 1 0 632108 323916 132928 1446032 0 0 3584 0 810 738 49 1 50 0 0 1 0 632108 317344 132928 1452544 0 0 3072 0 806 736 50 1 50 0 0 1 0 632108 310648 132928 1459120 0 0 3584 0 793 703 49 1 50 0 0 1 0 632108 304464 132928 1465488 0 0 3072 0 811 745 49 2 50 0 0 1 0 632108 297396 132936 1472068 0 0 3072 12 808 715 49 1 50 0 0 1 0 632108 290468 132936 1478876 0 0 3584 0 797 714 50 1 50 0 0 1 0 632108 284764 132944 1484132 0 0 3072 47284 996 776 50 2 43 6 0 2 0 632108 278564 132944 1490484 0 0 3072 0 813 720 58 1 41 0 0 1 0 632108 272480 132944 1496684 0 0 3072 32 822 742 56 2 43 0 0 1 0 632108 265800 132944 1503420 0 0 3584 0 826 743 50 1 50 0 0 1 0 632108 259592 132944 1509836 0 0 3072 0 798 742 49 1 50 0 0 1 0 632108 252772 132944 1516204 0 0 3072 0 771 716 58 2 40 0 0 1 0 632108 245952 132944 1523052 0 0 3584 4 785 699 50 1 50 0 0 procs -----------memory---------- ---swap-- -----io---- -system-- -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id wa st 1 0 632108 239380 132944 1529576 0 0 3072 21796 832 649 49 2 49 0 0 1 0 632108 233072 132944 1535908 0 0 3072 0 773 718 50 0 50 0 0 1 0 632108 226128 132944 1542780 0 0 3584 0 834 769 49 1 49 0 0 2 0 632108 219556 132944 1549556 0 0 3072 0 817 757 57 1 43 0 0 1 0 632108 213248 132944 1555864 0 0 3072 0 798 710 57 2 40 0 0 1 0 632108 206480 132944 1562492 0 0 3584 0 841 836 50 1 49 0 0 1 0 632108 200000 132944 1569012 0 0 3072 0 842 809 50 1 50 0 0 1 0 632108 193684 132944 1575320 0 0 3072 0 841 749 49 1 50 0 0 1 0 632108 188104 132956 1580916 0 0 3584 25540 923 772 49 3 46 3 0 novell@wdhsen01:~> vmstat 1 30 procs -----------memory---------- ---swap-- -----io---- -system-- -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id wa st 1 0 632108 45496 132808 1721868 1 1 10 33 2 2 1 0 99 0 0 2 0 632108 39420 132808 1728308 0 0 3072 0 817 747 58 1 42 0 0 1 0 632108 33344 132808 1734528 0 0 3072 0 760 670 56 1 43 0 0 1 0 632108 82200 132280 1685904 0 0 3584 12 751 679 49 2 49 1 0 1 0 632108 76496 132280 1692268 0 0 3072 0 739 672 48 2 50 0 0 1 0 632108 70172 132280 1698524 0 0 3072 28 767 691 50 1 50 0 0 1 0 632108 63152 132280 1705572 0 0 3584 0 785 727 49 1 50 0 0 1 0 632108 57208 132280 1711568 0 0 3072 0 784 722 49 2 49 0 0 2 0 632108 50884 132280 1717956 0 0 3072 0 804 711 50 1 50 0 0 1 0 632108 43956 132280 1724708 0 0 3584 0 815 779 49 1 50 0 0 2 0 632108 37772 132280 1731088 0 0 3072 32 814 724 56 1 43 0 0 1 0 632108 30456 132280 1738260 0 0 3584 0 790 700 60 1 40 0 0 1 0 632108 80056 131640 1689212 0 0 3072 0 795 716 49 2 50 0 0 1 0 632108 73740 131640 1695520 0 0 3072 0 832 803 50 1 50 0 0 1 0 632108 68664 131648 1700552 0 0 3072 47356 975 713 49 2 43 6 0 1 0 632108 61968 131652 1707292 0 0 3584 28 817 763 49 2 50 0 0 1 0 632108 56016 131652 1713424 0 0 3072 156 795 707 49 2 50 0 0 1 0 632108 49692 131652 1719764 0 0 3072 0 814 755 49 1 50 0 0 1 0 632108 42864 131652 1726720 0 0 3584 0 794 691 49 1 50 0 0 2 0 632108 36912 131652 1732716 0 0 3072 0 794 732 58 1 41 0 0 1 0 632108 91224 130992 1678484 0 0 3584 0 822 730 57 2 42 0 0 procs -----------memory---------- ---swap-- -----io---- -system-- -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id wa st 1 0 632108 84900 130992 1685028 0 0 3072 22412 924 761 49 1 50 0 0 1 0 632108 78576 130992 1691260 0 0 3072 0 843 776 49 1 50 0 0 1 0 632108 71756 130992 1698284 0 0 3584 0 865 792 50 0 50 0 0 1 0 632108 65308 130992 1704828 0 0 3072 0 861 770 49 2 50 0 0 1 0 632108 58324 130992 1711600 0 0 3584 0 866 905 49 1 50 0 0 1 0 632108 51852 130992 1718008 0 0 3072 0 813 754 49 1 50 0 0 1 0 632108 45496 130992 1724524 0 0 3072 0 812 737 49 1 50 0 0 4 0 632108 38560 130992 1731312 0 0 3584 0 816 737 49 2 50 0 0 2 0 632108 33228 131004 1736472 0 0 3072 26356 904 727 61 3 34 3 0 For small tables, it is not using 100% of CPU, but the same query with limit 1 is also taking 100% of CPU. Explain Analyze o/p is very big. I am pasting here: Unique (cost=133906741.63..133916648.38 rows=200 width=516) (actual time=486765.451..487236.949 rows=35 loops=1) -> Sort (cost=133906741.63..133911695.00 rows=1981350 width=516) (actual time=486765.450..487053.139 rows=1979735 loops=1) Sort Key: events_rpt_v3.init_service_comp Sort Method: external merge Disk: 46416kB -> Subquery Scan events_rpt_v3 (cost=131752986.71..133238999.21 rows=1981350 width=516) (actual time=452529.136..472555.577 rows=1979735 loops=1) -> Unique (cost=131752986.71..133219185.71 rows=1981350 width=73436) (actual time=452529.128..471365.258 rows=1979735 loops=1) -> Sort (cost=131752986.71..131757940.09 rows=1981350 width=73436) (actual time=452529.126..460179.820 rows=1979735 loops=1) Sort Key: public.events.evt_id, public.events.res, public.events.sres, public.events.sev, public.events.evt_time, public.events.evt_time, public.events.device_evt_time, public.events.sentinel_process_time, public.events.begin_time, public.events.end_time, public.events.repeat_cnt, public.events.dp_int, public.events.sp_int, public.events.msg, public.events.evt, public.events.et, public.events.cust_id, public.events.src_asset_id, public.events.dest_asset_id, public.events.agent_id, public.events.prtcl_id, public.events.arch_id, public.events.sip, (to_ip_char(public.events.sip)), public.events.shn, public.events.sp, public.events.dip, (to_ip_char(public.events.dip)), public.events.dhn, public.events.dp, public.events.sun, public.events.dun, public.events.fn, public.events.ei, public.events.init_usr_sys_id, public.events.init_usr_identity_guid, public.events.trgt_usr_sys_id, public.events.trgt_usr_identity_guid, Sort Method: external merge Disk: 1504224kB -> Append (cost=0.00..7167670.80 rows=1981350 width=73436) (actual time=188.427..432870.506 rows=1979735 loops=1) -> Result (cost=0.00..7117927.97 rows=1972965 width=73436) (actual time=188.427..431513.274 rows=1971352 loops=1) -> Append (cost=0.00..212550.47 rows=1972965 width=73436) (actual time=4.445..19200.613 rows=1971352 loops=1) -> Seq Scan on events (cost=0.00..10.00 rows=1 width=73436) (actual time=0.000..0.000 rows=0 loops=1) -> Seq Scan on events_p_max events (cost=0.00..10.00 rows=1 width=73436) (actual time=0.000..0.000 rows=0 loops=1) -> Seq Scan on events_p_20110617110000 events (cost=0.00..10.00 rows=1 width=73436) (actual time=0.000..0.000 rows=0 loops=1) -> Result (cost=0.00..29929.33 rows=8385 width=73436) (actual time=11.691..928.277 rows=8383 loops=1) -> Append (cost=0.00..581.83 rows=8385 width=73436) (actual time=11.194..94.103 rows=8383 loops=1) -> Seq Scan on hist_events (cost=0.00..10.00 rows=1 width=73436) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on hist_events_p_max hist_events (cost=0.00..10.00 rows=1 width=73436) (actual time=0.000..0.000 rows=0 loops=1) -> Seq Scan on hist_events_p_20101105112519 hist_events (cost=0.00..212.18 rows=2918 width=62004) (actual time=11.192..31.355 rows=2918 loops=1) -> Seq Scan on hist_events_p_20101107112519 hist_events (cost=0.00..169.64 rows=2764 width=65510) (actual time=7.605..24.603 rows=2764 loops=1) -> Seq Scan on hist_events_p_20101104112519 hist_events (cost=0.00..180.01 rows=2701 width=61827) (actual time=19.064..37.047 rows=2701 loops=1) Total runtime: 487423.797 ms I have started vaccuum analyzing all the partitions, I will run the query and post the results once it is done. -- View this message in context: http://postgresql.1045698.n5.nabble.com/100-CPU-Utilization-when-we-run-queries-tp4465765p4493567.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance