Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

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

by neilwatson (Curate)
on Apr 08, 2014 at 00:06 UTC ( #1081451=note: print w/ replies, xml ) Need Help??


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

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


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

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (13)
As of 2014-08-21 14:18 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (136 votes), past polls