Re: SELECT AND AGG huge tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi all,
Thanks for your advice and the link about posting my question in an
appropriate form.
Here are the info. I thank all of you in advance.

Best regards
Houman



Postgres version: 9.1.4
=================================================
Postgres.conf
max_connections = 100
shared_buffers = 8192MB
work_mem = 500MB
log_statement = 'none'
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
max_locks_per_transaction = 256

=================================================
Hardware: 
CPU Quad Core Intel CPU
processor	: 0-7
vendor_id	: GenuineIntel
cpu family	: 6
model		: 45
model name	: Intel(R) Core(TM) i7-3820 CPU @ 3.60GHz

Memory:
MemTotal:       32927920 kB

HDD:
OCZ VeloDrive - Solid-State-Disk - 600 GB - intern - PCI Express 2.0 x8
Multi-Level-Cell (MLC)
PCI Express 2.0 x8
========================IO/stat===================
iostat sdb1 1
Linux 3.2.0-23-generic (regula2) 	10/17/2012 	_x86_64_	(8 CPU)
Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sdb1              6.44       217.91       240.45 1956400373 2158777589
sdb1              0.00         0.00         0.00          0          0
sdb1              0.00         0.00         0.00          0          0
sdb1              0.00         0.00         0.00          0          0
sdb1              0.00         0.00         0.00          0          0
sdb1              0.00         0.00         0.00          0          0
=========================vmstat==========================
procs -----------memory---------- ---swap-- -----io---- -system--
----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id
wa
 1  0  44376 2417096 210784 28664024    0    0    30    35    0    0  0  0
100  0
 0  0  44376 2416964 210784 28664024    0    0     0     0   80  138  0  0
100  0
 1  0  44376 2416592 210784 28664024    0    0     0     0  278  228  7  0
93  0
 1  0  44376 2416592 210784 28664280    0    0     0     0  457  305 12  0
88  0
 1  0  44376 2416716 210784 28664280    0    0     0     0  472  303 12  0
88  0
 1  0  44376 2416716 210784 28664280    0    0     0     0  462  296 13  0
88  0
 1  0  44376 2416716 210784 28664280    0    0     0     0  478  293 12  0
88  0
 1  0  44376 2416716 210784 28664280    0    0     0     0  470  317 12  0
87  0
 1  0  44376 2416716 210784 28664280    0    0     0     0  455  299 12  0
88  0
 1  0  44376 2416716 210784 28664280    0    0     0     0  459  301 12  0
87  0
 1  0  44376 2416716 210784 28664280    0    0     0     0  370  291  7  5
88  0
 1  0  44376 2416716 210784 28664280    0    0     0    29  459  319 12  1
88  0
 1  0  44376 2416716 210784 28664280    0    0     0     0  453  295 12  0
88  0
 1  0  44376 2416716 210784 28664280    0    0     0     0  449  284 12  0
88  0
 1  0  44376 2416716 210784 28664280    0    0     0     8  462  304 12  0
88  0
 1  0  44376 2416716 210784 28664280    0    0     0     0  459  307 12  0
88  0
 2  0  44376 2416716 210784 28664280    0    0     0     0  461  300 12  0
88  0
 1  0  44376 2416716 210784 28664280    0    0     0     0  457  299 12  0
87  0
 1  0  44376 2416716 210784 28664280    0    0     0     0  439  295 12  0
88  0
 1  0  44376 2416716 210784 28664280    0    0     0     0  439  306 12  0
88  0
 1  0  44376 2416716 210784 28664280    0    0     0     0  448  305 12  0
88  0
 1  0  44376 2416716 210784 28664280    0    0     0     0  457  289 12  0
88  0
 0  0  44376 2416716 210784 28664280    0    0     0     0  174  179  3  0
97  0
 0  0  44376 2416716 210784 28664280    0    0     0     0   73  133  0  0
100  0
 0  0  44376 2416716 210784 28664280    0    0     0     0   75  133  0  0
100  0
 0  0  44376 2416716 210784 28664280    0    0     0     0   70  127  0  0
100  0



        Column         |            Type             |                      
Modifiers                       
-----------------------+-----------------------------+-------------------------------------------------------
 modifying_action      | integer                     | 
 modifying_client      | integer                     | 
 modification_time     | timestamp without time zone | 
 instance_entity       | integer                     | 
 id                    | integer                     | not null default
nextval('enigma.fact_seq'::regclass)
 successor             | integer                     | 
 reporting_date        | integer                     | 
 legal_entity          | integer                     | 
 client_system         | integer                     | 
 customer              | integer                     | 
 customer_type         | integer                     | 
 borrower              | integer                     | 
 nace                  | integer                     | 
 lsk                   | integer                     | 
 review_date           | integer                     | 
 uci_status            | integer                     | 
 rating                | integer                     | 
 rating_date           | integer                     | 
 asset_class_sta_flags | integer                     | 
 asset_class_flags     | integer                     | 
 balance_indicator     | integer                     | 
 quantity              | integer                     | 
 credit_line           | numeric                     | 
 outstanding           | numeric                     | 
 ead                   | numeric                     | 
 ead_collateralized    | numeric                     | 
 ead_uncollateralized  | numeric                     | 
 el                    | numeric                     | 
 rwa                   | numeric                     | 
 lgd                   | numeric                     | 
 pd                    | numeric                     | 
 economic_capital      | numeric                     | 
 unit                  | integer                     | 
========================================================================
Indexes:
 "fact_pkey" PRIMARY KEY, btree (id)
 "enigma_fact_id_present" UNIQUE CONSTRAINT, btree (id)
 "indx_enigma_fact_legal_entity" btree (legal_entity)
 "indx_enigma_fact_reporting_date" btree (reporting_date)
Triggers:
    fact_before_update_referrers_trigger BEFORE DELETE ON enigma.fact FOR
EACH ROW EXECUTE PROCEDURE enigma.fact_update_referrers_function()
========================================================================
genesis=# SELECT count(*) FROM enigma.fact;
  count  
---------
 7493958
========================================================================
EXPLAIN analyze SELECT                 
SUM(T.quantity) AS T__quantity, 
SUM(T.credit_line) AS T__credit_line, 
SUM(T.outstanding) AS T__outstanding, 
SUM(T.ead) AS T__ead, 
SUM(T.ead_collateralized) AS T__ead_collateralized, 
SUM(T.ead_uncollateralized) AS T__ead_uncollateralized, 
SUM(T.el) AS T__el, 
SUM(T.rwa) AS T__rwa, 
AVG(T.lgd) AS T__lgd, 
AVG(T.pd) AS T__pd
FROM enigma.fact T 
GROUP BY T.legal_entity 
ORDER BY T.legal_entity;
----------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=1819018.32..1819018.36 rows=15 width=48) (actual
time=20436.264..20436.264 rows=15 loops=1)
   Sort Key: legal_entity
   Sort Method: quicksort  Memory: 27kB
   ->  HashAggregate  (cost=1819017.80..1819018.02 rows=15 width=48) (actual
time=20436.221..20436.242 rows=15 loops=1)
         ->  Seq Scan on fact t  (cost=0.00..959291.68 rows=31262768
width=48) (actual time=2.619..1349.523 rows=7493958 loops=1)
 Total runtime: 20436.410 ms

========================================================================

EXPLAIN (BUFFERS true, ANALYZE)  SELECT SUM(T.quantity) AS T__quantity, 
SUM(T.credit_line) AS T__credit_line, 
SUM(T.outstanding) AS T__outstanding, 
SUM(T.ead) AS T__ead, 
SUM(T.ead_collateralized) AS T__ead_collateralized, 
SUM(T.ead_uncollateralized) AS T__ead_uncollateralized, 
SUM(T.el) AS T__el, 
SUM(T.rwa) AS T__rwa, 
AVG(T.lgd) AS T__lgd, 
AVG(T.pd) AS T__pd
FROM enigma.fact T 
GROUP BY T.legal_entity 
ORDER BY T.legal_entity;
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=1819018.32..1819018.36 rows=15 width=48) (actual
time=20514.976..20514.977 rows=15 loops=1)
   Sort Key: legal_entity
   Sort Method: quicksort  Memory: 27kB
   Buffers: shared hit=2315 read=644351
   ->  HashAggregate  (cost=1819017.80..1819018.02 rows=15 width=48) (actual
time=20514.895..20514.917 rows=15 loops=1)
         Buffers: shared hit=2313 read=644351
         ->  Seq Scan on fact t  (cost=0.00..959291.68 rows=31262768
width=48) (actual time=2.580..1385.491 rows=7493958 loops=1)
               Buffers: shared hit=2313 read=644351
 Total runtime: 20515.369 ms


                                                           QUERY PLAN                                                            




--
View this message in context: http://postgresql.1045698.n5.nabble.com/SELECT-AND-AGG-huge-tables-tp5728306p5728572.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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux