Created
October 15, 2009 16:14
-
-
Save EvanCarroll/211061 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| UPDATE inventory.vehicles AS v | |
| SET decode_color_ext1 = c.ext1_desc | |
| , decode_color_image = c.jpg_320 | |
| FROM chrome.view_image AS c | |
| WHERE v.color_code_ext1 IS NOT null | |
| AND v.chrome_styleid = c.fkey_style | |
| AND v.color_code_ext1 = c.ext1_mfr_full | |
| ; | |
| dealermade=# select pg_class.relname,pg_locks.* from pg_class,pg_locks where pg_class.relfilenode=pg_locks.relation order by relname; | |
| relname | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | |
| ------------------------------+----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+--------- | |
| chrome_images__rgb_hex | relation | 17700 | 1568753 | | | | | | | | 1/6429830 | 15664 | AccessShareLock | t | |
| color | relation | 17700 | 1559475 | | | | | | | | 1/6429830 | 15664 | AccessShareLock | t | |
| color | relation | 17700 | 1568745 | | | | | | | | 1/6429830 | 15664 | AccessShareLock | t | |
| color__ext_mfr | relation | 17700 | 1559487 | | | | | | | | 1/6429830 | 15664 | AccessShareLock | t | |
| color__ext_mfr_full | relation | 17700 | 1559488 | | | | | | | | 1/6429830 | 15664 | AccessShareLock | t | |
| color__ext_rgb_hex | relation | 17700 | 1559489 | | | | | | | | 1/6429830 | 15664 | AccessShareLock | t | |
| color__fkey_style | relation | 17700 | 1559486 | | | | | | | | 1/6429830 | 15664 | AccessShareLock | t | |
| color__int_mfr | relation | 17700 | 1559490 | | | | | | | | 1/6429830 | 15664 | AccessShareLock | t | |
| color_chrome_styleid_key | relation | 17700 | 1568751 | | | | | | | | 1/6429830 | 15664 | AccessShareLock | t | |
| pg_class | relation | 17700 | 1259 | | | | | | | | 2/171844 | 15720 | AccessShareLock | t | |
| pg_class_oid_index | relation | 17700 | 2662 | | | | | | | | 2/171844 | 15720 | AccessShareLock | t | |
| pg_class_relname_nsp_index | relation | 17700 | 2663 | | | | | | | | 2/171844 | 15720 | AccessShareLock | t | |
| pg_locks | relation | 17700 | 10969 | | | | | | | | 2/171844 | 15720 | AccessShareLock | t | |
| vehicles | relation | 17700 | 1500441 | | | | | | | | 1/6429830 | 15664 | RowExclusiveLock | t | |
| vehicles__vin | relation | 17700 | 1500451 | | | | | | | | 1/6429830 | 15664 | RowExclusiveLock | t | |
| vehicles__vin_substr | relation | 17700 | 1500452 | | | | | | | | 1/6429830 | 15664 | RowExclusiveLock | t | |
| vehicles_pkey | relation | 17700 | 1500449 | | | | | | | | 1/6429830 | 15664 | RowExclusiveLock | t | |
| vhiecles__vin_textpatternops | relation | 17700 | 1500453 | | | | | | | | 1/6429830 | 15664 | RowExclusiveLock | t | |
| view_color | relation | 17700 | 1568759 | | | | | | | | 1/6429830 | 15664 | AccessShareLock | t | |
| view_image | relation | 17700 | 1569683 | | | | | | | | 1/6429830 | 15664 | AccessShareLock | t | |
| (20 rows) | |
| The EXPLAIN ANALYZE | |
| QUERY PLAN | |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
| Hash Left Join (cost=173887.84..259005.32 rows=1030 width=809) (actual time=18786.845..19122.510 rows=11313 loops=1) | |
| Hash Cond: (nvd.fkey_style = image.chrome_styleid) | |
| Join Filter: (((image.ext1_mfr_full IS NULL) OR (image.ext1_mfr_full = nvd.ext1_mfr_full)) AND ((image.ext2_mfr_full IS NULL) OR (image.ext2_mfr_full = nvd.ext2_mfr_full)) AND ((image.ext1_rgb_hex IS NULL) OR (image.ext1_rgb_hex = nvd.ext1_rgb_hex)) AND ((image.ext2_rgb_hex IS NULL) OR (image.ext2_rgb_hex = nvd.ext2_rgb_hex))) | |
| -> Hash Join (cost=164531.80..246489.68 rows=294 width=801) (actual time=17751.835..17875.449 rows=10399 loops=1) | |
| Hash Cond: ((v.chrome_styleid = nvd.fkey_style) AND (v.color_code_ext1 = nvd.ext1_mfr_full)) | |
| -> Seq Scan on vehicles v (cost=0.00..2609.40 rows=3855 width=761) (actual time=0.069..74.458 rows=4036 loops=1) | |
| Filter: (color_code_ext1 IS NOT NULL) | |
| -> Hash (cost=142743.52..142743.52 rows=1452552 width=40) (actual time=17745.297..17745.297 rows=1396871 loops=1) | |
| -> Seq Scan on color nvd (cost=0.00..142743.52 rows=1452552 width=40) (actual time=0.112..14487.139 rows=1452552 loops=1) | |
| -> Hash (cost=8393.35..8393.35 rows=77015 width=164) (actual time=1034.743..1034.743 rows=77015 loops=1) | |
| -> Subquery Scan image (cost=0.00..8393.35 rows=77015 width=164) (actual time=0.091..867.366 rows=77015 loops=1) | |
| -> Seq Scan on color (cost=0.00..7623.20 rows=77015 width=35) (actual time=0.086..687.494 rows=77015 loops=1) | |
| Total runtime: 19860.942 ms | |
| (13 rows) | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment