From: | Josh Goldberg <josh(at)4dmatrix(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | experimenting with coalesce, strange EXPLAIN results |
Date: | 2003-09-04 23:07:08 |
Message-ID: | 3F57C59C.9060600@4dmatrix.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
I was experimenting with moving some of my case logic out of the
application and into sql and found a strange EXPLAIN result with one of
my COALESCE statements. Why is each subselect listed twice? This is in
7.3.4 on freebsd 4.8.
cms3=# explain analyze select coalesce ((sELECT thumb FROM content_group_descriptor WHERE content_group_id = 140 AND language_id = 1),(SELECT tc.file FROM thumbnail_content tc, ccl WHERE tc.parent_content_id = cid AND ccgid = 140 limit 1));
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.98..0.98 rows=1 loops=1)
InitPlan
-> Seq Scan on content_group_descriptor (cost=0.00..2.78 rows=1 width=4) (actual time=0.10..0.10 rows=0 loops=1)
Filter: ((content_group_id = 140) AND (language_id = 1))
-> Seq Scan on content_group_descriptor (cost=0.00..2.78 rows=1 width=4) (never executed)
Filter: ((content_group_id = 140) AND (language_id = 1))
-> Limit (cost=0.00..8.43 rows=1 width=12) (actual time=0.35..0.42 rows=1 loops=1)
-> Nested Loop (cost=0.00..14.94 rows=2 width=12) (actual time=0.34..0.40 rows=2 loops=1)
-> Seq Scan on content_collection (cost=0.00..5.46 rows=1 width=4) (actual time=0.24..0.26 rows=3 loops=1)
Filter: (content_collection_group_id = 140)
-> Index Scan using parent_file_key on thumbnail_content tc (cost=0.00..6.83 rows=1 width=8) (actual time=0.02..0.02 rows=1 loops=3)
Index Cond: (tc.parent_content_id = "outer".content_id)
-> Limit (cost=0.00..8.43 rows=1 width=12) (actual time=0.32..0.39 rows=1 loops=1)
-> Nested Loop (cost=0.00..14.94 rows=2 width=12) (actual time=0.31..0.37 rows=2 loops=1)
-> Seq Scan on content_collection (cost=0.00..5.46 rows=1 width=4) (actual time=0.24..0.26 rows=3 loops=1)
Filter: (content_collection_group_id = 140)
-> Index Scan using parent_file_key on thumbnail_content tc (cost=0.00..6.83 rows=1 width=8) (actual time=0.01..0.02 rows=1 loops=3)
Index Cond: (tc.parent_content_id = "outer".content_id)
Total runtime: 1.14 msec
(19 rows)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-09-04 23:24:04 | Re: max_fsm_pages |
Previous Message | Tom Lane | 2003-09-04 22:57:19 | Re: Recovery assistence.... |