Database Administrators

Return to Question

    Bumped by Community user
    Bumped by Community user
    Bumped by Community user
    Bumped by Community user
5 deleted 20 characters in body
source | link
inline side-by-side side-by-side markdown 
"UpdateUpdate on pages  (cost=65108.49..1545071.72 rows=4037902 width=331) (actual time=48181951.584..48181951.584 rows=0 loops=1)"
"  ->  Hash Join  (cost=65108.49..1545071.72 rows=4037902 width=331) (actual time=4075.973..1200902.835 rows=1909499 loops=1)"
"        Hash Cond: ((pages."urlShort")::text = sites.url)"
"        ->  Seq Scan on pages  (cost=0.00..1056057.95 rows=4037902 width=317) (actual time=0.025..456909.895 rows=2053904 loops=1)"
"              Filter: ((id_site IS NULL) AND ("labelDate" < '2015-09-01'::date))"
"              Rows Removed by Filter: 12105346"12105346
"        ->  Hash  (cost=30907.66..30907.66 rows=1606466 width=41) (actual time=4061.106..4061.106 rows=1606489 loops=1)"
"              Buckets: 2097152  Batches: 2  Memory Usage: 74179kB"74179kB
"              ->  Seq Scan on sites  (cost=0.00..30907.66 rows=1606466 width=41) (actual time=0.024..869.068 rows=1606489 loops=1)"
"PlanningPlanning time: 3.767 ms"ms
"ExecutionExecution time: 48181966.394 ms"ms

"Update on pages  (cost=65108.49..1545071.72 rows=4037902 width=331) (actual time=48181951.584..48181951.584 rows=0 loops=1)"
"  ->  Hash Join  (cost=65108.49..1545071.72 rows=4037902 width=331) (actual time=4075.973..1200902.835 rows=1909499 loops=1)"
"        Hash Cond: ((pages."urlShort")::text = sites.url)"
"        ->  Seq Scan on pages  (cost=0.00..1056057.95 rows=4037902 width=317) (actual time=0.025..456909.895 rows=2053904 loops=1)"
"              Filter: ((id_site IS NULL) AND ("labelDate" < '2015-09-01'::date))"
"              Rows Removed by Filter: 12105346"
"        ->  Hash  (cost=30907.66..30907.66 rows=1606466 width=41) (actual time=4061.106..4061.106 rows=1606489 loops=1)"
"              Buckets: 2097152  Batches: 2  Memory Usage: 74179kB"
"              ->  Seq Scan on sites  (cost=0.00..30907.66 rows=1606466 width=41) (actual time=0.024..869.068 rows=1606489 loops=1)"
"Planning time: 3.767 ms"
"Execution time: 48181966.394 ms"

Update on pages  (cost=65108.49..1545071.72 rows=4037902 width=331) (actual time=48181951.584..48181951.584 rows=0 loops=1)
  ->  Hash Join  (cost=65108.49..1545071.72 rows=4037902 width=331) (actual time=4075.973..1200902.835 rows=1909499 loops=1)
        Hash Cond: ((pages."urlShort")::text = sites.url)
        ->  Seq Scan on pages  (cost=0.00..1056057.95 rows=4037902 width=317) (actual time=0.025..456909.895 rows=2053904 loops=1)
              Filter: ((id_site IS NULL) AND ("labelDate" < '2015-09-01'::date))
              Rows Removed by Filter: 12105346
        ->  Hash  (cost=30907.66..30907.66 rows=1606466 width=41) (actual time=4061.106..4061.106 rows=1606489 loops=1)
              Buckets: 2097152  Batches: 2  Memory Usage: 74179kB
              ->  Seq Scan on sites  (cost=0.00..30907.66 rows=1606466 width=41) (actual time=0.024..869.068 rows=1606489 loops=1)
Planning time: 3.767 ms
Execution time: 48181966.394 ms
    Bumped by Community user
    Bumped by Community user
4 Added detailed statistics.
source | link
inline side-by-side side-by-side markdown

Based on some help in the past on related subjects I decidedThere are two things I'd like to compare this with a similar query that used a correlated subquery instead of a join.know:

  1. Can I adjust the update to run faster based on the above?
  2. What parts of the query plan are telltales for running slow? Or do you always have to run EXPLAIN ANALYZE to find it out?

Here's the output from running EXPLAIN ANALYZE during the update:

SELECT "urlShort" AS"Update url
FROMon pages
WHERE "labelDate" = '2015-01-01'
(cost=65108.49..1545071.72 rows=4037902 width=331) (actual ANDtime=48181951.584..48181951.584 EXISTSrows=0 (SELECTloops=1)"
" * FROM-> sites WHEREHash sites.urlJoin = pages(cost=65108."urlShort")

This query only takes about 15s to run and has the following query plan:

Hash49..1545071.72 Joinrows=4037902 width=331) (cost=64418actual time=4075.96973..8504191200902.64835 rows=477167rows=1909499 width=27loops=1)"
"        Hash Cond: ((pages."urlShort")::text = sites.url)"
"        ->  Bitmap HeapSeq Scan on pages  (cost=13430cost=0.4800..7928701056057.1195 rows=477167rows=4037902 width=27width=317) (actual time=0.025..456909.895 rows=2053904 loops=1)"
"        Recheck Cond     Filter: ((id_site IS NULL) AND ("labelDate" =< '2015-0109-01'::date))"
"              Rows Removed by Filter: 12105346"
"        ->  BitmapHash Index Scan on(cost=30907.66..30907.66 "pages_labelDate_idx"rows=1606466 width=41) (cost=0actual time=4061.00106..133114061.19106 rows=477167rows=1606489 width=0loops=1)"
 "             Index CondBuckets: ("labelDate"2097152 = '2015-01-01':Batches:date)
 2 -> Memory HashUsage: 74179kB"
" (cost=30907.66..30907.66 rows=1606466 width=27)
           ->  Seq Scan on sites  (cost=0.00..30907.66 rows=1606466 width=27width=41) (actual time=0.024..869.068 rows=1606489 loops=1)"
"Planning time: 3.767 ms"
"Execution time: 48181966.394 ms"

There are two things I'd likeI've tried different memory settings (from 128MB to know:1024MB shared_buffers) but they don't seem to make much difference.

  1. Can adjust the update to run faster based on the above?
  2. What parts of the query plan are telltales for running slow? Or do you always have to run EXPLAIN ANALYZE to find it out?

Based on some help in the past on related subjects I decided to compare this with a similar query that used a correlated subquery instead of a join.

SELECT "urlShort" AS url
FROM pages
WHERE "labelDate" = '2015-01-01'
    AND EXISTS (SELECT * FROM sites WHERE sites.url = pages."urlShort")

This query only takes about 15s to run and has the following query plan:

Hash Join  (cost=64418.96..850419.64 rows=477167 width=27)
  Hash Cond: ((pages."urlShort")::text = sites.url)
  ->  Bitmap Heap Scan on pages  (cost=13430.48..792870.11 rows=477167 width=27)
        Recheck Cond: ("labelDate" = '2015-01-01'::date)
        ->  Bitmap Index Scan on "pages_labelDate_idx"  (cost=0.00..13311.19 rows=477167 width=0)
              Index Cond: ("labelDate" = '2015-01-01'::date)
  ->  Hash  (cost=30907.66..30907.66 rows=1606466 width=27)
        ->  Seq Scan on sites  (cost=0.00..30907.66 rows=1606466 width=27)

There are two things I'd like to know:

  1. Can adjust the update to run faster based on the above?
  2. What parts of the query plan are telltales for running slow? Or do you always have to run EXPLAIN ANALYZE to find it out?

There are two things I'd like to know:

  1. Can I adjust the update to run faster based on the above?
  2. What parts of the query plan are telltales for running slow? Or do you always have to run EXPLAIN ANALYZE to find it out?

Here's the output from running EXPLAIN ANALYZE during the update:

"Update on pages  (cost=65108.49..1545071.72 rows=4037902 width=331) (actual time=48181951.584..48181951.584 rows=0 loops=1)"
"  ->  Hash Join  (cost=65108.49..1545071.72 rows=4037902 width=331) (actual time=4075.973..1200902.835 rows=1909499 loops=1)"
"        Hash Cond: ((pages."urlShort")::text = sites.url)"
"        ->  Seq Scan on pages  (cost=0.00..1056057.95 rows=4037902 width=317) (actual time=0.025..456909.895 rows=2053904 loops=1)"
"              Filter: ((id_site IS NULL) AND ("labelDate" < '2015-09-01'::date))"
"              Rows Removed by Filter: 12105346"
"        ->  Hash  (cost=30907.66..30907.66 rows=1606466 width=41) (actual time=4061.106..4061.106 rows=1606489 loops=1)"
"              Buckets: 2097152  Batches: 2  Memory Usage: 74179kB"
"              ->  Seq Scan on sites  (cost=0.00..30907.66 rows=1606466 width=41) (actual time=0.024..869.068 rows=1606489 loops=1)"
"Planning time: 3.767 ms"
"Execution time: 48181966.394 ms"

I've tried different memory settings (from 128MB to 1024MB shared_buffers) but they don't seem to make much difference.
3 added 37 characters in body
source | link
inline side-by-side side-by-side markdown

I've started workworking on adapting the import logic to work with a more normalised schema – no surprises here it's faster and more compact – but I've hit a roadblock updating the existing data: adding and updating with relevant foreign keys is taking an age.

UPDATE pages
SET id_site = id  
FROM sites
WHERE sites.url = pages."urlShort"
    AND "labelDate" = '2015-01-15'

NB pages."urlShort"pages."urlShort" and sites.urlsites.url are textfieldstext fields, both are indexed but currently have no explicit relationship.

There are around 500,000 rows for each date value and updates like this are taking around 2h302.5 hours. :-(

select *  
from pages
join sites on
sites.url = pages."urlShort"
where "labelDate" = '2015-01-01'

SELECT "urlShort" AS url
FROM pages
WHERE 
 "labelDate" = '2015-01-01'
    AND EXISTS
  (SELECT * FROM sites
     WHERE sites.url = pages."urlShort")

There are two things I'd like to know: 1) Can adjust the update to run faster based on the above? 2) What parts of the query plan are telltales for running slow? Or do you always have to run EXPLAIN ANALYZE to findout?

  1. Can adjust the update to run faster based on the above?
  2. What parts of the query plan are telltales for running slow? Or do you always have to run EXPLAIN ANALYZE to find it out?

I've started work on adapting the import logic to work with a more normalised schema – no surprises here it's faster and more compact – but I've hit a roadblock updating the existing data: adding and updating with relevant foreign keys is taking an age.

UPDATE pages
SET id_site = id FROM sites
WHERE sites.url = pages."urlShort"
AND "labelDate" = '2015-01-15'

NB pages."urlShort" and sites.url are textfields, both are indexed but currently have no explicit relationship.

There are around 500,000 rows for each date value and updates like this are taking around 2h30. :-(

select * from pages
join sites on
sites.url = pages."urlShort"
where "labelDate" = '2015-01-01'

SELECT "urlShort" AS url
FROM pages
WHERE 
 "labelDate" = '2015-01-01'
AND EXISTS
 (SELECT * FROM sites
     WHERE sites.url = pages."urlShort")

There are two things I'd like to know: 1) Can adjust the update to run faster based on the above? 2) What parts of the query plan are telltales for running slow? Or do you always have to run EXPLAIN ANALYZE to findout?

I've started working on adapting the import logic to work with a more normalised schema – no surprises here it's faster and more compact – but I've hit a roadblock updating the existing data: adding and updating with relevant foreign keys is taking an age.

UPDATE pages
SET id_site = id  
FROM sites
WHERE sites.url = pages."urlShort"
    AND "labelDate" = '2015-01-15'

NB pages."urlShort" and sites.url are text fields, both are indexed but currently have no explicit relationship.

There are around 500,000 rows for each date value and updates like this are taking around 2.5 hours. :-(

select *  
from pages
join sites on
sites.url = pages."urlShort"
where "labelDate" = '2015-01-01'

SELECT "urlShort" AS url
FROM pages
WHERE "labelDate" = '2015-01-01'
    AND EXISTS (SELECT * FROM sites WHERE sites.url = pages."urlShort")

There are two things I'd like to know:

  1. Can adjust the update to run faster based on the above?
  2. What parts of the query plan are telltales for running slow? Or do you always have to run EXPLAIN ANALYZE to find it out?
2 Corrected query and query plan.
source | link
1
source | link