I'm just starting out using VIEWs -- and mostly new to postgresql and
I'm trying to learn a little about reading the EXPLAIN ANALYZE output.
I have the following view setup and I'm wonder where to create
indexes, and mostly if I'm doing anything the incorrect or a very
expensive way.
CREATE VIEW class_list
(
id, name, class_time, location, workshop, review_mode, workshop_group,
location_name,
address, city, state, zip,
region, region_name
)
AS
SELECT class.id, class.name, class.class_time, class.location, class.workshop,
class.review_mode, class.workshop_group,
location.name,
address.id, address.city, address.state, address.zip,
region.id, region.name
FROM class, location, address, region
WHERE class.location = location.id
AND location.address = address.id
AND location.region = region.id;
I'm not clear about the Seq Scan below. The region
table is quite small, so am I correct that is why the planner is doing
a seq scan on that table?
\d region
Table "public.region"
Column | Type | Modifiers
------------+---------+--------------------------------------------------------
id | integer | not null default nextval('public.region_id_seq'::text)
active | boolean | not null default true
sort_order | integer | not null default 1
name | text | not null
Indexes:
"region_pkey" primary key, btree (id)
"region_name_key" unique, btree (name)
EXPLAIN ANALYZE select * from class_list where workshop = 28;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=51.78..93.07 rows=9 width=157) (actual time=1.306..1.468 rows=6 loops=1)
-> Hash Join (cost=51.78..76.87 rows=8 width=129) (actual time=1.245..1.299 rows=6 loops=1)
Hash Cond: ("outer".id = "inner".region)
-> Seq Scan on region (cost=0.00..20.00 rows=1000 width=36) (actual time=0.016..0.027 rows=10 loops=1)
-> Hash (cost=51.76..51.76 rows=8 width=97) (actual time=1.019..1.019 rows=0 loops=1)
-> Hash Join (cost=26.68..51.76 rows=8 width=97) (actual time=0.201..1.007 rows=6 loops=1)
Hash Cond: ("outer".id = "inner"."location")
-> Seq Scan on "location" (cost=0.00..20.00 rows=1000 width=44) (actual time=0.014..0.694 rows=104 loops=1)
-> Hash (cost=26.66..26.66 rows=7 width=57) (actual time=0.150..0.150 rows=0 loops=1)
-> Index Scan using class_workshop_index on "class" (cost=0.00..26.66 rows=7 width=57) (actual time=0.057..0.137 rows=6 loops=1)
Index Cond: (workshop = 28)
-> Index Scan using address_pkey on address (cost=0.00..2.01 rows=1 width=32) (actual time=0.013..0.015 rows=1 loops=6)
Index Cond: ("outer".address = address.id)
Total runtime: 1.853 ms
(14 rows)
By the way -- at one point I managed to hang postgresql (7.4.8-16 on
Debian Sid). I have not been able to make it happen again, but it
seemed odd.
(gdb) bt
#0 0x081e51ee in tuplestore_gettuple ()
#1 0x0810c7f0 in ExecMaterial ()
#2 0x08102cb2 in ExecProcNode ()
#3 0x0810d8d5 in ExecNestLoop ()
#4 0x08102ceb in ExecProcNode ()
#5 0x081093a4 in ExecAgg ()
#6 0x08102c79 in ExecProcNode ()
#7 0x08101ecc in ExecutorRun ()
#8 0x0816f58b in PortalSetResultFormat ()
#9 0x0816f8c7 in PortalRun ()
#10 0x0816da9f in PostgresMain ()
#11 0x08148b4e in ClosePostmasterPorts ()
#12 0x0814a4e1 in PostmasterMain ()
#13 0x0811c2e7 in main ()
--
Bill Moseley
moseley@xxxxxxxx
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
