Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Re: Help on selecting and deleting many records in a DB.

by RichardK (Priest)
on Apr 05, 2014 at 16:31 UTC ( #1081262=note: print w/ replies, xml ) Need Help??


in reply to Help on selecting and deleting many records in a DB.

Doing the work in the database is almost always faster. Did you try running postgresql's EXPLAIN on your query?

Do you have enough indexes on your table?


Comment on Re: Help on selecting and deleting many records in a DB.
Re^2: Help on selecting and deleting many records in a DB.
by neilwatson (Curate) on Apr 05, 2014 at 17:02 UTC
    Since a unique record is based on a combination of most fields the current indexes do not work well for this operation. Would adding another field, that is a concatenation of the other fields, and indexing that be a good approach?

    Neil Watson
    watson-wilson.ca

      No, you need to create an index that matches your query terms. See: http://www.postgresql.org/docs/9.1/static/indexes.html

      You can try this :

      delete from agent_log t1 where WHERE timestamp < now() - interval '7 days') and timespan < (select max(timestamp) from agent_log t2 where t2.class=t1.class and t2.hostname=t1.hostname and t2.id_address=t1.ip_address)

      I suppose you have an index on timestamp and the other fields? A surrogate key might help, or even using timespan as primary key, if possible.

      If you can create a history table, then you could:

      SELECT class, max(timestamp) as timestamp, hostname, ip_address, promise_handle, promiser, promisee, policy_server, promise_outcome FROM agent_log WHERE timestamp < now() - interval '7 days' into history_table ; delete from agent_log WHERE timestamp < now() - interval '7 days'

      That would be a good start towards partitioning your data, which you might need to consider.



      libremen.com : legal case, contract and insurance claim management software
        SELECT * FROM agent_log t1 WHERE timestamp < now() - interval '7 days' AND timestamp < ( SELECT max(timestamp) FROM agent_log t2 WHERE t2.class=t1.class AND t2.promiser=t1.promiser AND t2.hostname=t1.hostname AND t2.ip_address=t1.ip_address ) ORDER by class,timestamp;
        You almost had me there, but I don't think this will keep the maximum timestamp per day.

        Neil Watson
        watson-wilson.ca

Re^2: Help on selecting and deleting many records in a DB.
by neilwatson (Curate) on Apr 08, 2014 at 00:06 UTC

    I did some testing with a small sampling of data. Explain and analyze below.

    Seq Scan on agent_log (cost=48184.35..52265496.54 rows=101705 width=122) (actual time=5288.903..175884.135 rows=203428 loops=1)
    Filter: (("timestamp" < (now() - '7 days'::interval)) AND (NOT (SubPlan 1)))
    SubPlan 1
    -> Materialize (cost=48184.35..48646.77 rows=20342 width=77) (actual time=0.022..0.317 rows=2604 loops=203428)
    -> GroupAggregate (cost=44040.21..47905.01 rows=20342 width=77) (actual time=4417.518..5286.975 rows=2604 loops=1)
    -> Sort (cost=44040.21..44548.73 rows=203410 width=77) (actual time=4417.496..5223.285 rows=203428 loops=1)
    Sort Key: (date_trunc('day'::text, public.agent_log."timestamp")), public.agent_log.class, public.agent_log.promiser, public.agent_log.hostname, public.agent_log.ip_address
    Sort Method: external merge Disk: 19648kB
    -> Seq Scan on agent_log (cost=0.00..8024.51 rows=203410 width=77) (actual time=0.011..175.827 rows=203428 loops=1)
    Filter: ("timestamp" < (now() - '7 days'::interval))
    Total runtime: 175924.695 ms

    Neil Watson
    watson-wilson.ca

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1081262]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (10)
As of 2014-12-27 20:13 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (177 votes), past polls