Re: Performance issues when the number of records are around 10 Million - Mailing list pgsql-general

On May 11, 12:03 pm, andreas.kretsch...@schollglas.com ("A.
Kretschmer") wrote:
> In response to venu madhav :
>
>
>
> > Hi all,
> >        In my database application, I've a table whose records can
> > reach 10M and insertions can happen at a faster rate like 100
> > insertions per second in the peak times. I configured postgres to do
> > auto vacuum on hourly basis. I have frontend GUI application in CGI
> > which displays the data from the database.
> >         When I try to get the last twenty records from the database,
> > it takes around 10-15 mins to complete the operation.This is the query
> > which is used:
>
> > select e.cid, timestamp, s.sig_class, s.sig_priority, s.sig_name,
> > e.sniff_ip, e.sniff_channel, s.sig_config, e.wifi_addr_1,
> > e.wifi_addr_2, e.view_status, bssid  FROM event e, signature s WHERE
> > s.sig_id = e.signature   AND e.timestamp >= '1270449180' AND
> > e.timestamp < '1273473180'  ORDER BY e.cid DESC,  e.cid DESC limit 21
> > offset 10539780;
>
> First, show us the table-definition for both tables.
> Secondly the output generated from EXPLAIN ANALYSE <your query>
[Venu Madhav]
-------------------------------- event schema
-------------------------------------------------------------
snort=# \d event;
                  Table "public.event"
       Column       |         Type          | Modifiers
--------------------+-----------------------+-----------
 sid                | integer               | not null
 cid                | bigint                | not null
 sig_name           | character varying(80) | not null
 signature          | integer               | not null
 sig_class          | character varying(80) | not null
 sig_priority       | bigint                |
 timestamp          | bigint                | not null
 sniff_ip           | character varying(16) |
 sniff_channel      | smallint              |
 bssid              | character varying(18) |
 view_status        | smallint              |
 wifi_ver           | smallint              | not null
 wifi_type          | smallint              | not null
 wifi_stype         | smallint              | not null
 wifi_other_fc_bits | smallint              | not null
 wifi_dur_id        | integer               | not null
 wifi_addr_1        | character varying(18) |
 wifi_addr_2        | character varying(18) |
 wifi_addr_3        | character varying(18) |
 wifi_addr_4        | character varying(18) |
 wifi_seq_ctrl      | integer               |
Indexes:
    "event_pkey" PRIMARY KEY, btree (cid)
    "cid_idx" btree (cid)
    "signature_idx" btree (signature)
    "timestamp_idx" btree ("timestamp")
    "wifi_addr_1_idx" btree (wifi_addr_1)
    "wifi_addr_2_idx" btree (wifi_addr_2)
Foreign-key constraints:
    "event_fkey_sid" FOREIGN KEY (sid) REFERENCES sensor(sid) ON
UPDATE CASCADE
    "event_fkey_signature" FOREIGN KEY (signature) REFERENCES
signature(sig_id) ON UPDATE CASCADE

----------------------------------------------- schema of signature
----------------------------------
snort=# \d signature;
                                     Table "public.signature"
    Column    |         Type          |
Modifiers
--------------+-----------------------
+------------------------------------------------------------
 sig_id       | integer               | not null default
nextval('signature_sig_id_seq'::regclass)
 sig_name     | character varying(80) | not null
 sig_class    | character varying(80) | not null
 sig_priority | bigint                |
 sig_rev      | bigint                |
 sig_sid      | bigint                |
 sig_config   | text                  |
Indexes:
    "signature_pkey" PRIMARY KEY, btree (sig_id)
    "sig_class_idx" btree (sig_class)
    "sig_name_idx" btree (sig_name)

-------------------------- Explain Analyse of the query
-------------------------------------------
nort=# EXPLAIN ANALYZE select e.cid, timestamp, s.sig_class,
s.sig_priority, s.sig_name, e.sniff_ip, e.sniff_channel, s.sig_config,
e.wifi_addr_1,  e.wifi_addr_2, e.view_status, bssid  FROM event e,
signature s WHERE s.sig_id = e.signature   AND e.timestamp >=
'1270449180' AND e.timestamp < '1273473180'  ORDER BY e.cid DESC,
e.cid DESC limit 21 offset 10539780;
                                                                 QUERY
PLAN

---------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=7885743.98..7885743.98 rows=1 width=287) (actual
time=1462193.060..1462193.083 rows=14 loops=1)
   ->  Sort  (cost=7859399.66..7885743.98 rows=10537727 width=287)
(actual time=1349648.207..1456496.334 rows=10539794 loops=1)
         Sort Key: e.cid
         ->  Hash Join  (cost=2.44..645448.31 rows=10537727 width=287)
(actual time=0.182..139745.001 rows=10539794 loops=1)
               Hash Cond: ("outer".signature = "inner".sig_id)
               ->  Seq Scan on event e  (cost=0.00..487379.97
rows=10537727 width=104) (actual time=0.012..121595.257 rows=10539794
loops=1)
                     Filter: (("timestamp" >= 1270449180::bigint) AND
("timestamp" < 1273473180::bigint))
               ->  Hash  (cost=2.35..2.35 rows=35 width=191) (actual
time=0.097..0.097 rows=36 loops=1)
                     ->  Seq Scan on signature s  (cost=0.00..2.35
rows=35 width=191) (actual time=0.005..0.045 rows=36 loops=1)
 Total runtime: 1463829.145 ms
(10 rows)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> I'm surprised about the "e.timestamp >= '1270449180'", is this a
> TIMESTAMP-column?
[Venu Madhav] Yes, it is timestamp in epoch time.
>
> And, to retrieve the last twenty records you should write:
>
> ORDER BY ts DESC LIMIT 20
>
> With a proper index on this column this should force an index-scan.
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general