Hi,
The following large EXPLAIN SELECT Statement fails to return, but
continues to take up processing time until it is killed.
I've straightened up the postgresql.conf, as per Tom Lane's suggestions
since last time I wrote about this.
See: http://archives.postgresql.org/pgsql-general/2005-12/msg00259.php
Any and all help, greatly appreciated. Thank you.
David Link, White Plains, NY
Environment:
Linux 2.6.5-7.191 SMP
Postgresql 7.4.8
Database size: 110 Gb on disk.
vacuum analyze done
Processes:
postgres 15687 1 0 16:12 pts/1 00:00:00 /usr/bin/postmaster -D
/db/pgsql
postgres 15693 15687 0 16:12 pts/1 00:00:00 postgres: stats buffer
process
postgres 15695 15693 0 16:12 pts/1 00:00:00 postgres: stats
collector process
postgres 17485 15687 99 16:17 pts/1 00:18:17 postgres: dlink usbkup
[local] EXPLAIN
Configuration:
tcpip_socket = true
max_connections = 200
shared_buffers = 8000
sort_mem = 262144
vacuum_mem = 65536
max_fsm_pages = 200000
fsync = false
wal_sync_method = fsync
checkpoint_segments = 30
effective_cache_size = 131072
random_page_cost = 4
geqo = true
geqo_threshold = 13
default_statistics_target = 100
from_collapse_limit = 10
join_collapse_limit = 10
log_timestamp = true
stats_start_collector = true
stats_command_string = true
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
The SQL:
explain
select
t.tid,
t.title,
to_char (t.retail_reldate, 'mm-dd-yy') as retail_reldate,
coalesce (s200401.units, 0) as s200401u,
coalesce (s200402.units, 0) as s200402u,
coalesce (s200403.units, 0) as s200403u,
coalesce (s200404.units, 0) as s200404u,
coalesce (s200405.units, 0) as s200405u,
coalesce (s200406.units, 0) as s200406u,
coalesce (s200407.units, 0) as s200407u,
coalesce (s200408.units, 0) as s200408u,
coalesce (s200409.units, 0) as s200409u,
coalesce (s200410.units, 0) as s200410u,
coalesce (s200411.units, 0) as s200411u,
coalesce (s200412.units, 0) as s200412u,
coalesce (s200413.units, 0) as s200413u,
coalesce (s200414.units, 0) as s200414u,
coalesce (s200415.units, 0) as s200415u,
coalesce (s200416.units, 0) as s200416u,
coalesce (s200417.units, 0) as s200417u,
coalesce (s200418.units, 0) as s200418u,
coalesce (s200419.units, 0) as s200419u,
coalesce (s200420.units, 0) as s200420u,
coalesce (s200421.units, 0) as s200421u,
coalesce (s200422.units, 0) as s200422u,
coalesce (s200423.units, 0) as s200423u,
coalesce (s200424.units, 0) as s200424u,
coalesce (s200425.units, 0) as s200425u,
coalesce (s200426.units, 0) as s200426u,
coalesce (s200427.units, 0) as s200427u,
coalesce (s200428.units, 0) as s200428u,
coalesce (s200429.units, 0) as s200429u,
coalesce (s200430.units, 0) as s200430u,
coalesce (s200431.units, 0) as s200431u,
coalesce (s200432.units, 0) as s200432u,
coalesce (s200433.units, 0) as s200433u,
coalesce (s200434.units, 0) as s200434u,
coalesce (s200435.units, 0) as s200435u,
coalesce (s200436.units, 0) as s200436u,
coalesce (s200437.units, 0) as s200437u,
coalesce (s200438.units, 0) as s200438u,
coalesce (s200439.units, 0) as s200439u,
coalesce (s200440.units, 0) as s200440u,
coalesce (s200441.units, 0) as s200441u,
coalesce (s200442.units, 0) as s200442u,
coalesce (s200443.units, 0) as s200443u,
coalesce (s200444.units, 0) as s200444u,
coalesce (s200445.units, 0) as s200445u,
coalesce (s200446.units, 0) as s200446u,
coalesce (s200447.units, 0) as s200447u,
coalesce (s200448.units, 0) as s200448u,
coalesce (s200449.units, 0) as s200449u,
coalesce (s200450.units, 0) as s200450u,
coalesce (s200451.units, 0) as s200451u,
coalesce (s200452.units, 0) as s200452u
from
title t
left outer join sale_200401 s200401 on t.tid=s200401.tid and
s200401.channel=100
left outer join sale_200402 s200402 on t.tid=s200402.tid and
s200402.channel=100
left outer join sale_200403 s200403 on t.tid=s200403.tid and
s200403.channel=100
left outer join sale_200404 s200404 on t.tid=s200404.tid and
s200404.channel=100
left outer join sale_200405 s200405 on t.tid=s200405.tid and
s200405.channel=100
left outer join sale_200406 s200406 on t.tid=s200406.tid and
s200406.channel=100
left outer join sale_200407 s200407 on t.tid=s200407.tid and
s200407.channel=100
left outer join sale_200408 s200408 on t.tid=s200408.tid and
s200408.channel=100
left outer join sale_200409 s200409 on t.tid=s200409.tid and
s200409.channel=100
left outer join sale_200410 s200410 on t.tid=s200410.tid and
s200410.channel=100
left outer join sale_200411 s200411 on t.tid=s200411.tid and
s200411.channel=100
left outer join sale_200412 s200412 on t.tid=s200412.tid and
s200412.channel=100
left outer join sale_200413 s200413 on t.tid=s200413.tid and
s200413.channel=100
left outer join sale_200414 s200414 on t.tid=s200414.tid and
s200414.channel=100
left outer join sale_200415 s200415 on t.tid=s200415.tid and
s200415.channel=100
left outer join sale_200416 s200416 on t.tid=s200416.tid and
s200416.channel=100
left outer join sale_200417 s200417 on t.tid=s200417.tid and
s200417.channel=100
left outer join sale_200418 s200418 on t.tid=s200418.tid and
s200418.channel=100
left outer join sale_200419 s200419 on t.tid=s200419.tid and
s200419.channel=100
left outer join sale_200420 s200420 on t.tid=s200420.tid and
s200420.channel=100
left outer join sale_200421 s200421 on t.tid=s200421.tid and
s200421.channel=100
left outer join sale_200422 s200422 on t.tid=s200422.tid and
s200422.channel=100
left outer join sale_200423 s200423 on t.tid=s200423.tid and
s200423.channel=100
left outer join sale_200424 s200424 on t.tid=s200424.tid and
s200424.channel=100
left outer join sale_200425 s200425 on t.tid=s200425.tid and
s200425.channel=100
left outer join sale_200426 s200426 on t.tid=s200426.tid and
s200426.channel=100
left outer join sale_200427 s200427 on t.tid=s200427.tid and
s200427.channel=100
left outer join sale_200428 s200428 on t.tid=s200428.tid and
s200428.channel=100
left outer join sale_200429 s200429 on t.tid=s200429.tid and
s200429.channel=100
left outer join sale_200430 s200430 on t.tid=s200430.tid and
s200430.channel=100
left outer join sale_200431 s200431 on t.tid=s200431.tid and
s200431.channel=100
left outer join sale_200432 s200432 on t.tid=s200432.tid and
s200432.channel=100
left outer join sale_200433 s200433 on t.tid=s200433.tid and
s200433.channel=100
left outer join sale_200434 s200434 on t.tid=s200434.tid and
s200434.channel=100
left outer join sale_200435 s200435 on t.tid=s200435.tid and
s200435.channel=100
left outer join sale_200436 s200436 on t.tid=s200436.tid and
s200436.channel=100
left outer join sale_200437 s200437 on t.tid=s200437.tid and
s200437.channel=100
left outer join sale_200438 s200438 on t.tid=s200438.tid and
s200438.channel=100
left outer join sale_200439 s200439 on t.tid=s200439.tid and
s200439.channel=100
left outer join sale_200440 s200440 on t.tid=s200440.tid and
s200440.channel=100
left outer join sale_200441 s200441 on t.tid=s200441.tid and
s200441.channel=100
left outer join sale_200442 s200442 on t.tid=s200442.tid and
s200442.channel=100
left outer join sale_200443 s200443 on t.tid=s200443.tid and
s200443.channel=100
left outer join sale_200444 s200444 on t.tid=s200444.tid and
s200444.channel=100
left outer join sale_200445 s200445 on t.tid=s200445.tid and
s200445.channel=100
left outer join sale_200446 s200446 on t.tid=s200446.tid and
s200446.channel=100
left outer join sale_200447 s200447 on t.tid=s200447.tid and
s200447.channel=100
left outer join sale_200448 s200448 on t.tid=s200448.tid and
s200448.channel=100
left outer join sale_200449 s200449 on t.tid=s200449.tid and
s200449.channel=100
left outer join sale_200450 s200450 on t.tid=s200450.tid and
s200450.channel=100
left outer join sale_200451 s200451 on t.tid=s200451.tid and
s200451.channel=100
left outer join sale_200452 s200452 on t.tid=s200452.tid and
s200452.channel=100
where
t.blob in ('L', 'M', 'R')
and t.source_dvd != 'IN'
order by
4
