--B_3188627745_60556641
Content-type: text/plain;
charset="US-ASCII"
Content-transfer-encoding: 7bit

Hi Michael,

Here you go:

This is the create table statement:

CREATE TABLE jobstat_lc_q4_2004 (
jobstatid_q4_2004 serial NOT NULL,
jobid integer,
fetchtime timestamp without time zone NOT NULL,
stime timestamp without time zone,
mtime timestamp without time zone,
status character(1),
cpu_time integer,
req_jobfs bigint,
used_jobfs bigint,
req_walltime integer,
curr_walltime integer,
sys_time time without time zone,
vmemlim bigint,
used_vmem bigint,
used_mem bigint,
nodesdown text,
sys_timeint integer,
curr_cpu bigint,
curr_sys bigint
);

-- Grants
REVOKE ALL ON TABLE jobstat_lc_q3_2004 FROM PUBLIC;
GRANT SELECT ON TABLE jobstat_lc_q3_2004 TO apache;

-- Indexes
CREATE INDEX jobstat_lc_q4_2004_jobid ON jobstat_lc_q4_2004 USING btree
(jobid);
CREATE INDEX jobstat_lc_q4_2004_fetchtime ON jobstat_lc_q4_2004 USING btree
(fetchtime);
CREATE UNIQUE INDEX jobstat_lc_q4_2004_walltime ON
unq_jobstat_lc_q4_2004_jobid_fetch USING btree (jobid, fetchtime);


When I wrote the email, I tried this on a new empty table and I was able to
reproduce it, but I can't seem to reproduce it again (I don't remember the
exact CREATE statement I used + the indexes I created). Any way, since this
is the actual 'production' table in the database, and this is where the
problem happens every time, I'll give you a bit more information about the
table. The table has around 6040854 rows:

hpc=> SELECT COUNT(*) FROM jobstat_lc_q4_2004;
count
---------
6040854
(1 row)

I'm usually doing a search on a particular 'jobid' in my application, which
has no idea whether a jobid actually exists or not (as the id is provided by
the user). Running the following query with a bogus (non-existing) jobid
gets stuck forever (or for a very very very long time):

hpc=> SELECT jobid, curr_walltime, status, fetchtime FROM jobstat_lc_q4_2004
WHERE jobid = 123123 AND curr_walltime != 0 ORDER BY fetchtime DESC LIMIT 1;

If I run the same command *without* 'LIMIT 1', it returns instantly. I've
attached the log from pg_lock, as you requested, as well when I ran the
above query. Hope something can be sorted out.

Thanks for your time.

Regards,
Fahad


On 15/1/05 5:18 AM, "Michael Fuhr" <mike (AT) fuhr (DOT) org> wrote:

--
main(){int j=12345;char t[]=":aAbcdefFgGhijklmnNopqrsStuUvwyz \n",
*i="dUGScUiAbpmwqbmgduAvpmmlzce\nlmGGUbFbzjdb";whi le(*i){j+=
strchr(t,*i++)-t;j%=sizeof t-1;putchar(t[j]);}return 0;}



--B_3188627745_60556641
Content-type: application/octet-stream; name="lock_log.rtf"
Content-disposition: attachment;
filename="lock_log.rtf"
Content-transfer-encoding: base64

e1xydGYxXG1hY1xhbnNpY3BnMTAwMDBcY29jb2FydGYxMDIKe1 xmb250dGJs
XGYwXGZzd2lzc1xmY2hhcnNldDc3IEhlbHZldGljYTt9CntcY2 9sb3J0Ymw7
XHJlZDI1NVxncmVlbjI1NVxibHVlMjU1O30KXG1hcmdsMTQ0MF xtYXJncjE0
NDBcdmlld3cxNzU4MFx2aWV3aDEzNDgwXHZpZXdraW5kMApccG FyZFx0eDU2
Nlx0eDExMzNcdHgxNzAwXHR4MjI2N1x0eDI4MzRcdHgzNDAxXH R4Mzk2OFx0
eDQ1MzVcdHg1MTAyXHR4NTY2OVx0eDYyMzZcdHg2ODAzXHFsXH FuYXR1cmFs
CgpcZjBcZnMyNCBcY2YwIGhwYz0+IFNFTEVDVCByZWxhdGlvbj o6cmVnY2xh
c3MsICogRlJPTSBwZ19sb2NrcztcCiAgICAgICAgICAgcmVsYX Rpb24gICAg
ICAgICAgICAgICAgfCByZWxhdGlvbiAgICAgICAgIHwgZGF0YW Jhc2UgICAg
IHwgdHJhbnNhY3Rpb24gICB8ICBwaWQgICAgICB8ICAgICAgIG 1vZGUgICAg
ICAgICAgICAgICAgICAgfCBncmFudGVkIFwKLS0tLS0tLS0tLS 0tLS0tLS0t
LS0tLS0tLS0tLS0tKy0tLS0tLS0tLS0tLS0tLS0tKy0tLS0tLS 0tLS0tLS0t
LS0rLS0tLS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tKy0tLS0tLS 0tLS0tLS0t
LS0tLS0tLS0tLS0tKy0tLS0tLS0tLS0tXAogICAgICAgICAgIC AgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgIHwgICAgICAgICAgICAgIC AgICAgICAg
IHwgICAgICAgICAgICAgICAgICAgICAgIHwgICAgIDE5Mzc2Nz UgIHwgMTM0
NjAgfCBFeGNsdXNpdmVMb2NrICAgICAgICAgfCB0XAogdW5xX2 pvYmlkX2pv
Yl9zYyAgICAgICB8IDEwNDQwNzEzNiB8ICAgICAxNzIzMCAgIC AgICB8ICAg
ICAgICAgICAgICAgICAgICAgICB8IDEzNDYwIHwgQWNjZXNzU2 hhcmVMb2Nr
ICB8IHRcCiAxMTk2MzAyODkgICAgICAgICAgICAgICAgICAgfC AxMTk2MzAy
ODkgfCAxMTk2Mjk5MzAgfCAgICAgICAgICAgICAgICAgICAgIC AgfCAxNjE4
OSB8IEFjY2Vzc1NoYXJlTG9jayAgfCB0XAogMTE5NjMwNTE4IC AgICAgICAg
ICAgICAgICAgIHwgMTE5NjMwNTE4IHwgMTE5NjI5OTMwIHwgIC AgICAgICAg
ICAgICAgICAgICAgIHwgMzAyMTYgfCBBY2Nlc3NTaGFyZUxvY2 sgIHwgdFwK
IGpvYnN0YXRfc2NfcTFfMjAwNSAgIHwgMTA5NjMzMTE2IHwgIC AgIDE3MjMw
ICAgICAgfCAgICAgICAgICAgICAgICAgICAgICAgfCAxMzQ2MC B8IEFjY2Vz
c1NoYXJlTG9jayAgfCB0XAogMTE5NjMwMjcyICAgICAgICAgIC AgICAgICAg
IHwgMTE5NjMwMjcyIHwgMTE5NjI5OTMwIHwgICAgICAgICAgIC AgICAgICAg
ICAgIHwgMTYxODkgfCBBY2Nlc3NTaGFyZUxvY2sgIHwgdFwKIC AgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICB8ICAgIC AgICAgICAg
ICAgICAgICAgICB8ICAgICAgICAgICAgICAgICAgICAgICB8IC AgICAxODg0
ODAzICB8IDMwMjE2IHwgRXhjbHVzaXZlTG9jayAgICAgICAgIH wgdFwKIDEx
OTYzMDgzOSAgICAgICAgICAgICAgICAgICB8IDExOTYzMDgzOS B8IDExOTYy
OTkzMCB8ICAgICAgICAgICAgICAgICAgICAgICB8IDMwMjE2IH wgQWNjZXNz
U2hhcmVMb2NrICB8IHRcCiAxMTk2MzAyNDcgICAgICAgICAgIC AgICAgICAg
fCAxMTk2MzAyNDcgfCAxMTk2Mjk5MzAgfCAgICAgICAgICAgIC AgICAgICAg
ICAgfCAzMDIxNiB8IEFjY2Vzc1NoYXJlTG9jayAgfCB0XAogMT E5NjMwMjg1
ICAgICAgICAgICAgICAgICAgIHwgMTE5NjMwMjg1IHwgMTE5Nj I5OTMwIHwg
ICAgICAgICAgICAgICAgICAgICAgIHwgMzAyMTYgfCBBY2Nlc3 NTaGFyZUxv
Y2sgIHwgdFwKIGpvYl9zYyAgICAgICAgICAgICAgICAgICAgIC AgICAgICB8
ICAgICAxNzQ2NCAgICAgIHwgICAgIDE3MjMwICAgICAgIHwgIC AgICAgICAg
ICAgfCAxNzEzNyB8IEFjY2Vzc1NoYXJlTG9jayAgfCB0XAogMT E5NjMwMjY4
ICAgICAgICAgICAgICAgICAgIHwgMTE5NjMwMjY4IHwgMTE5Nj I5OTMwIHwg
ICAgICAgICAgICAgfCAzMDIxNiB8IEFjY2Vzc1NoYXJlTG9jay AgfCB0XAog
am9iX3NjICAgICAgICAgICAgICAgICAgICAgICAgICAgIHwgIC AgIDE3NDY0
IHwgICAgIDE3MjMwIHwgICAgICAgICAgICAgICAgICAgICAgIH wgMTM0NjAg
fCBBY2Nlc3NTaGFyZUxvY2sgIHwgdFwKIGpvYl9zYyAgICAgIC AgICAgICAg
ICAgICAgICAgICAgICB8ICAgICAxNzQ2NCB8ICAgICAxNzIzMC B8ICAgICAg
ICAgICAgICAgICAgICAgICB8IDEzNDYwIHwgUm93U2hhcmVMb2 NrICAgICB8
IHRcCiBqb2Jfc2MgICAgICAgICAgICAgICAgICAgICAgICAgIC AgfCAgICAg
MTc0NjQgfCAgICAgMTcyMzAgfCAgICAgICAgICAgICAgICAgIC AgICAgfCAx
MzQ2MCB8IFJvd0V4Y2x1c2l2ZUxvY2sgfCB0XAogcGdfbG9ja3 MgICAgICAg
ICAgICAgICAgICAgICAgICB8ICAgICAxNjgzOSB8ICAgICAxNz IzMCB8ICAg
ICAgICAgICAgICAgICAgICAgICB8ICA2MDg3IHwgQWNjZXNzU2 hhcmVMb2Nr
ICB8IHRcCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIC AgICAgICAg
ICAgfCAgICAgICAgICAgICAgICAgIHwgICAgICAgICAgICAgIC AgICB8ICAg
ICAxODc3NzI1IHwgMTYxODkgfCBFeGNsdXNpdmVMb2NrICAgIH wgdFwKIDEx
OTYzMDI5OSAgICAgICAgICAgICAgICAgICAgfCAxMTk2MzAyOT kgfCAxMTk2
Mjk5MzAgfCAgICAgICAgICAgICB8IDMwMjE2IHwgQWNjZXNzU2 hhcmVMb2Nr
ICB8IHRcCiAxMTk2MzAyNjYgICAgICAgICAgICAgICAgICAgIH wgMTE5NjMw
MjY2IHwgMTE5NjI5OTMwIHwgICAgICAgICAgICAgfCAzMDIxNi B8IEFjY2Vz
c1NoYXJlTG9jayAgfCB0XAogMTE5NjMwMjkzICAgICAgICAgIC AgICAgICAg
ICB8IDExOTYzMDI5MyB8IDExOTYyOTkzMCB8ICAgICAgICAgIC AgIHwgMzAy
MTYgfCBBY2Nlc3NTaGFyZUxvY2sgIHwgdFwKIGpvYmNoYXJzLn VzZXJfYWxl
cnRzICAgICAgICAgfCAgICAgMTczMDEgfCAgICAgMTcyMzAgfC AgICAgICAg
ICAgICB8IDE3MTM2IHwgQWNjZXNzU2hhcmVMb2NrICB8IHRcCi AxMTk2MzA4
MjcgICAgICAgICAgICAgICAgICAgIHwgMTE5NjMwODI3IHwgMT E5NjI5OTMw
IHwgICAgICAgICAgICAgfCAxNjE4OSB8IEFjY2Vzc1NoYXJlTG 9jayAgfCB0
XAogMTE5NjMwODQyICAgICAgICAgICAgICAgICAgICB8IDExOT YzMDg0MiB8
IDExOTYyOTkzMCB8ICAgICAgICAgICAgIHwgMTYxODkgfCBBY2 Nlc3NTaGFy
ZUxvY2sgIHwgdFwKIGpvYmNoYXJzLnVzZXJfYWxlcnRzICAgIC AgICAgfCAg
ICAgMTczMDEgfCAgICAgMTcyMzAgfCAgICAgICAgICAgICB8ID E3MTM3IHwg
QWNjZXNzU2hhcmVMb2NrICB8IHRcCiBqb2JfbGMgICAgICAgIC AgICAgICAg
ICAgICAgIHwgICAgIDE3NDU1IHwgICAgIDE3MjMwIHwgICAgIC AgICAgICAg
fCAxNzEzNiB8IEFjY2Vzc1NoYXJlTG9jayAgfCB0XAogICAgIC AgICAgICAg
ICAgICAgICAgICAgICAgICB8ICAgICAgICAgICB8ICAgICAgIC AgICB8ICAg
ICAxOTM3NjY4IHwgIDY3NTQgfCBFeGNsdXNpdmVMb2NrICAgIH wgdFwKIDEx
OTYzMDMwMyAgICAgICAgICAgICAgICAgICAgfCAxMTk2MzAzMD MgfCAxMTk2
Mjk5MzAgfCAgICAgICAgICAgICB8IDMwMjE2IHwgQWNjZXNzU2 hhcmVMb2Nr
ICB8IHRcCiAxMTk2MzAzMTEgICAgICAgICAgICAgICAgICAgIH wgMTE5NjMw
MzExIHwgMTE5NjI5OTMwIHwgICAgICAgICAgICAgfCAxNjE4OS B8IEFjY2Vz
c1NoYXJlTG9jayAgfCB0XAogMTE5NjMwMjY2ICAgICAgICAgIC AgICAgICAg
ICB8IDExOTYzMDI2NiB8IDExOTYyOTkzMCB8ICAgICAgICAgIC AgIHwgMTYx
ODkgfCBBY2Nlc3NTaGFyZUxvY2sgIHwgdFwKIGpvYmNoYXJzLm 5vdGlmaWNh
dGlvbl9xdWV1ZSAgfCAgICAgMTcyODggfCAgICAgMTcyMzAgfC AgICAgICAg
ICAgICB8IDE3MTM3IHwgQWNjZXNzU2hhcmVMb2NrICB8IHRcCi AxMTk2MzAy
NzAgICAgICAgICAgICAgICAgICAgIHwgMTE5NjMwMjcwIHwgMT E5NjI5OTMw
IHwgICAgICAgICAgICAgfCAzMDIxNiB8IEFjY2Vzc1NoYXJlTG 9jayAgfCB0
XAogMTE5NjMwMjg1ICAgICAgICAgICAgICAgICAgICB8IDExOT YzMDI4NSB8
IDExOTYyOTkzMCB8ICAgICAgICAgICAgIHwgMTYxODkgfCBBY2 Nlc3NTaGFy
ZUxvY2sgIHwgdFwKIDExOTYzMDMyMSAgICAgICAgICAgICAgIC AgICAgfCAx
MTk2MzAzMjEgfCAxMTk2Mjk5MzAgfCAgICAgICAgICAgICB8ID E2MTg5IHwg
QWNjZXNzU2hhcmVMb2NrICB8IHRcCiBub2RlX3NjICAgICAgIC AgICAgICAg
ICAgICAgIHwgICAgIDE3NjM5IHwgICAgIDE3MjMwIHwgICAgIC AgICAgICAg
fCAxMzQ2MCB8IEFjY2Vzc1NoYXJlTG9jayAgfCB0XAogMTE5Nj MwODI3ICAg
ICAgICAgICAgICAgICAgICB8IDExOTYzMDgyNyB8IDExOTYyOT kzMCB8ICAg
ICAgICAgICAgIHwgMzAyMTYgfCBBY2Nlc3NTaGFyZUxvY2sgIH wgdFwKIHBn
X3R5cGUgICAgICAgICAgICAgICAgICAgICAgfCAgICAgIDEyND cgfCAxMTk2
Mjk5MzAgfCAgICAgICAgICAgICB8IDMwMjE2IHwgQWNjZXNzU2 hhcmVMb2Nr
ICB8IHRcCiAxMTk2MzAyOTMgICAgICAgICAgICAgICAgICAgIH wgMTE5NjMw
MjkzIHwgMTE5NjI5OTMwIHwgICAgICAgICAgICAgfCAxNjE4OS B8IEFjY2Vz
c1NoYXJlTG9jayAgfCB0XAogMTE5NjMwODM5ICAgICAgICAgIC AgICAgICAg
ICB8IDExOTYzMDgzOSB8IDExOTYyOTkzMCB8ICAgICAgICAgIC AgIHwgMTYx
ODkgfCBBY2Nlc3NTaGFyZUxvY2sgIHwgdFwKIDExOTYzMDgzMC AgICAgICAg
ICAgICAgICAgICAgfCAxMTk2MzA4MzAgfCAxMTk2Mjk5MzAgfC AgICAgICAg
ICAgICB8IDMwMjE2IHwgQWNjZXNzU2hhcmVMb2NrICB8IHRcCi Bqb2JzdGF0
X2xjX3E0XzIwMDRfZmV0Y2h0aW1lIHwgMTA0NDA3Mjc2IHwgIC AgIDE3MjMw
IHwgICAgICAgICAgICAgfCAgNjc1NCB8IEFjY2Vzc1NoYXJlTG 9jayAgfCB0
XAogICAgICAgICAgICAgICAgICAgICAgICAgICAgICB8ICAgIC AgICAgICB8
ICAgICAgICAgICB8ICAgICAxOTM3Njc2IHwgMTcxMzcgfCBFeG NsdXNpdmVM
b2NrICAgIHwgdFwKIHBnX3R5cGUgICAgICAgICAgICAgICAgIC AgICAgfCAg
ICAgIDEyNDcgfCAxMTk2Mjk5MzAgfCAgICAgICAgICAgICB8ID E2MTg5IHwg
QWNjZXNzU2hhcmVMb2NrICB8IHRcCiAxMTk2MzA4NTEgICAgIC AgICAgICAg
ICAgICAgIHwgMTE5NjMwODUxIHwgMTE5NjI5OTMwIHwgICAgIC AgICAgICAg
fCAzMDIxNiB8IEFjY2Vzc1NoYXJlTG9jayAgfCB0XAogMTE5Nj MwMzIxICAg
ICAgICAgICAgICAgICAgICB8IDExOTYzMDMyMSB8IDExOTYyOT kzMCB8ICAg
ICAgICAgICAgIHwgMzAyMTYgfCBBY2Nlc3NTaGFyZUxvY2sgIH wgdFwKIGFj
dHZfam9ic3RhdF9zY19xMV8yMDA1ICAgICAgfCAxMDk2MzMwND AgfCAgICAg
MTcyMzAgfCAgICAgICAgICAgICB8IDEzNDYwIHwgQWNjZXNzU2 hhcmVMb2Nr
ICB8IHRcCiBhY3R2X2pvYnN0YXRfc2NfcTFfMjAwNSAgICAgIH wgMTA5NjMz
MDQwIHwgICAgIDE3MjMwIHwgICAgICAgICAgICAgfCAxMzQ2MC B8IFJvd0V4
Y2x1c2l2ZUxvY2sgfCB0XAogICAgICAgICAgICAgICAgICAgIC AgICAgICAg
ICB8ICAgICAgICAgICB8ICAgICAgICAgICB8ICAgICAxOTM3Nj c3IHwgIDYw
ODcgfCBFeGNsdXNpdmVMb2NrICAgIHwgdFwKIDExOTYzMDI3Mi AgICAgICAg
ICAgICAgICAgICAgfCAxMTk2MzAyNzIgfCAxMTk2Mjk5MzAgfC AgICAgICAg
ICAgICB8IDMwMjE2IHwgQWNjZXNzU2hhcmVMb2NrICB8IHRcCi AxMTk2MzA4
NTEgICAgICAgICAgICAgICAgICAgIHwgMTE5NjMwODUxIHwgMT E5NjI5OTMw
IHwgICAgICAgICAgICAgfCAxNjE4OSB8IEFjY2Vzc1NoYXJlTG 9jayAgfCB0
XAogMTE5NjMwMjY4ICAgICAgICAgICAgICAgICAgICB8IDExOT YzMDI2OCB8
IDExOTYyOTkzMCB8ICAgICAgICAgICAgIHwgMTYxODkgfCBBY2 Nlc3NTaGFy
ZUxvY2sgIHwgdFwKIGpvYnN0YXRfbGNfcTRfMjAwNCAgICAgIC AgICAgfCAg
ICAgMTc1NjUgfCAgICAgMTcyMzAgfCAgICAgICAgICAgICB8IC A2NzU0IHwg
QWNjZXNzU2hhcmVMb2NrICB8IHRcCiAxMTk2MzA1MTggICAgIC AgICAgICAg
ICAgICAgIHwgMTE5NjMwNTE4IHwgMTE5NjI5OTMwIHwgICAgIC AgICAgICAg
fCAxNjE4OSB8IEFjY2Vzc1NoYXJlTG9jayAgfCB0XAogMTE5Nj MwODMwICAg
ICAgICAgICAgICAgICAgICB8IDExOTYzMDgzMCB8IDExOTYyOT kzMCB8ICAg
ICAgICAgICAgIHwgMTYxODkgfCBBY2Nlc3NTaGFyZUxvY2sgIH wgdFwKICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgfCAgICAgICAgIC AgfCAgICAg
ICAgICAgfCAgICAgMTkzNzY3MiB8IDE3MTM2IHwgRXhjbHVzaX ZlTG9jayAg
ICB8IHRcCiAxMTk2MzAyODkgICAgICAgICAgICAgICAgICAgIH wgMTE5NjMw
Mjg5IHwgMTE5NjI5OTMwIHwgICAgICAgICAgICAgfCAzMDIxNi B8IEFjY2Vz
c1NoYXJlTG9jayAgfCB0XAogMTE5NjMwMzExICAgICAgICAgIC AgICAgICAg
ICB8IDExOTYzMDMxMSB8IDExOTYyOTkzMCB8ICAgICAgICAgIC AgIHwgMzAy
MTYgfCBBY2Nlc3NTaGFyZUxvY2sgIHwgdFwKIDExOTYzMDg0Mi AgICAgICAg
ICAgICAgICAgICAgfCAxMTk2MzA4NDIgfCAxMTk2Mjk5MzAgfC AgICAgICAg
ICAgICB8IDMwMjE2IHwgQWNjZXNzU2hhcmVMb2NrICB8IHRcCi Bqb2JfbGMg
ICAgICAgICAgICAgICAgICAgICAgIHwgICAgIDE3NDU1IHwgIC AgIDE3MjMw
IHwgICAgICAgICAgICAgfCAxNzEzNyB8IEFjY2Vzc1NoYXJlTG 9jayAgfCB0
XAogam9iX3NjICAgICAgICAgICAgICAgICAgICAgICB8ICAgIC AxNzQ2NCB8
ICAgICAxNzIzMCB8ICAgICAgICAgICAgIHwgMTcxMzYgfCBBY2 Nlc3NTaGFy
ZUxvY2sgIHwgdFwKIDExOTYzMDMwMyAgICAgICAgICAgICAgIC AgICAgfCAx
MTk2MzAzMDMgfCAxMTk2Mjk5MzAgfCAgICAgICAgICAgICB8ID E2MTg5IHwg
QWNjZXNzU2hhcmVMb2NrICB8IHRcCig2MCByb3dzKVwKXAp9

--B_3188627745_60556641
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

--B_3188627745_60556641--

On 15/1/05 9:55 AM, "Fahad G." <Fahad.Gilani (AT) anusf (DOT) anu.edu.au> wrote:
.....

Sorry about that, I pasted the wrong 'grants' from the log. This should be
...._q4_... Where it says q3.

Also, the following 'fails' as well:

hpc=> SELECT jobid, curr_walltime, status, fetchtime FROM jobstat_lc_q4_2004
WHERE jobid = 123123 ORDER BY fetchtime DESC LIMIT 1;

Or

hpc=> SELECT * FROM jobstat_lc_q4_2004 WHERE jobid = 123123 ORDER BY
fetchtime DESC LIMIT 1;

Cheers,
Fahad



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

"Fahad G." <Fahad.Gilani (AT) anusf (DOT) anu.edu.au> writes:
I bet it's choosing the wrong index. What does EXPLAIN show in each
case?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Fri, Jan 14, 2005 at 11:31:05PM -0500, Tom Lane wrote:
The last index is created on a different table -- should it be
created on the table we're working with? And if so, are the columns
(jobid, fetchtime) correct? The index name suggests otherwise.

I created the table and the two indexes (the third is on a different
table; creating it on this table didn't change anything), populated
the table with random data, and ANALYZEd it. Below are several
tests run on 8.0.0rc5; notice how case 4 is much slower than the
others. My random data probably doesn't have the same distribution
as Fahad's, but I appear to have duplicated the problem.


Case 1: jobid exists, no LIMIT

EXPLAIN ANALYZE SELECT * FROM jobstat_lc_q4_2004
WHERE jobid = 500 AND curr_walltime != 0 ORDER BY fetchtime;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=189.80..190.05 rows=98 width=149) (actual time=2.768..3.189 rows=94 loops=1)
Sort Key: fetchtime
-> Index Scan using jobstat_lc_q4_2004_jobid on jobstat_lc_q4_2004 (cost=0.00..186.56 rows=98 width=149) (actual time=0.099..1.727 rows=94 loops=1)
Index Cond: (jobid = 500)
Filter: (curr_walltime <> 0)
Total runtime: 3.851 ms
(6 rows)


Case 2: jobid exists, LIMIT

EXPLAIN ANALYZE SELECT * FROM jobstat_lc_q4_2004
WHERE jobid = 500 AND curr_walltime != 0 ORDER BY fetchtime LIMIT 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..33.22 rows=1 width=149) (actual time=6.659..6.664 rows=1 loops=1)
-> Index Scan using jobstat_lc_q4_2004_fetchtime on jobstat_lc_q4_2004 (cost=0.00..3255.97 rows=98 width=149) (actual time=6.644..6.644 rows=1 loops=1)
Filter: ((jobid = 500) AND (curr_walltime <> 0))
Total runtime: 6.900 ms
(4 rows)


Case 3: jobid doesn't exist, no LIMIT

EXPLAIN ANALYZE SELECT * FROM jobstat_lc_q4_2004
WHERE jobid = 9999 AND curr_walltime != 0 ORDER BY fetchtime;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=189.80..190.05 rows=98 width=149) (actual time=0.103..0.103 rows=0 loops=1)
Sort Key: fetchtime
-> Index Scan using jobstat_lc_q4_2004_jobid on jobstat_lc_q4_2004 (cost=0.00..186.56 rows=98 width=149) (actual time=0.064..0.064 rows=0 loops=1)
Index Cond: (jobid = 9999)
Filter: (curr_walltime <> 0)
Total runtime: 0.325 ms
(6 rows)


Case 4: jobid doesn't exist, LIMIT

EXPLAIN ANALYZE SELECT * FROM jobstat_lc_q4_2004
WHERE jobid = 9999 AND curr_walltime != 0 ORDER BY fetchtime LIMIT 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..33.22 rows=1 width=149) (actual time=684.957..684.957 rows=0 loops=1)
-> Index Scan using jobstat_lc_q4_2004_fetchtime on jobstat_lc_q4_2004 (cost=0.00..3255.97 rows=98 width=149) (actual time=684.937..684.937 rows=0 loops=1)
Filter: ((jobid = 9999) AND (curr_walltime <> 0))
Total runtime: 685.197 ms
(4 rows)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

I've simplified the test case to the following:

CREATE TABLE foo (
id integer NOT NULL,
value integer NOT NULL
);

INSERT INTO foo (id, value)
SELECT random() * 1000, random() * 1000
FROM generate_series(1, 100000);

CREATE INDEX foo_id_idx ON foo (id);
CREATE INDEX foo_value_idx ON foo (value);

VACUUM ANALYZE foo;

EXPLAIN ANALYZE SELECT * FROM foo WHERE id = -1 ORDER BY value;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Sort (cost=186.46..186.71 rows=99 width=8) (actual time=0.101..0.101 rows=0 loops=1)
Sort Key: value
-> Index Scan using foo_id_idx on foo (cost=0.00..183.18 rows=99 width=8) (actual time=0.067..0.067 rows=0 loops=1)
Index Cond: (id = -1)
Total runtime: 0.259 ms
(5 rows)

EXPLAIN ANALYZE SELECT * FROM foo WHERE id = -1 ORDER BY value LIMIT 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..25.79 rows=1 width=8) (actual time=631.964..631.964 rows=0 loops=1)
-> Index Scan using foo_value_idx on foo (cost=0.00..2552.75 rows=99 width=8) (actual time=631.942..631.942 rows=0 loops=1)
Filter: (id = -1)
Total runtime: 632.135 ms
(4 rows)

Maybe I don't understand something about what EXPLAIN is showing,
but why does Limit have an estimated cost of 0.00..25.79 when the
thing it's limiting has a cost of 0.00..2552.75? Is that the cost
of just the limit operation? Is it supposed to be the cumulative
cost of everything up to that point? Is the planner preferring
this plan because of the 25.79 cost?

A workaround appears to be:

EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM foo WHERE id = -1 ORDER BY value) AS s LIMIT 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=186.46..186.48 rows=1 width=8) (actual time=0.124..0.124 rows=0 loops=1)
-> Subquery Scan s (cost=186.46..187.70 rows=99 width=8) (actual time=0.110..0.110 rows=0 loops=1)
-> Sort (cost=186.46..186.71 rows=99 width=8) (actual time=0.099..0.099 rows=0 loops=1)
Sort Key: value
-> Index Scan using foo_id_idx on foo (cost=0.00..183.18 rows=99 width=8) (actual time=0.064..0.064 rows=0 loops=1)
Index Cond: (id = -1)
Total runtime: 0.313 ms
(7 rows)

I see that the Limit in this query has an estimated cost of
186.46..186.48, so I'm still wondering why the Limit in the previous
query had a cost of 0.00..25.79. Is that my ignorance about how
the planner works, or is it a bug?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Michael Fuhr <mike (AT) fuhr (DOT) org> writes:
This represents the planner assuming that the indexscan will only need
to be run 1/99th of the way to completion. That is, having estimated
that there were 99 matching rows to be found, it assumes those are
uniformly distributed in the index-by-value, and that the scan can stop
as soon as the first one is found.

Since in reality there aren't *any* matching rows, the index scan has to
go all the way to the end :-(. Even if there were matching rows, they
might be much further out in the index order than the
uniform-distribution hypothesis predicts, because the id and value
columns might have been correlated.

Basically, what you're looking at here is that the planner is thinking
it should go for a fast-start plan in a scenario where that bet loses.
It's still a good bet though. I'm not sure how to formulate the notion
that there's too much risk of a slow result in this scenario.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Sun, Jan 16, 2005 at 02:56:11PM -0500, Tom Lane wrote:
Thanks -- I understood the rationale for considering a scan on this
index but not why that plan was preferred. Your explanation provides
the piece I was missing.

Would it be accurate to say that the planner makes the bet most
likely to win without regard to how badly it might lose? Is taking
the downside into consideration a tough problem to solve, or is it
simply not worthwhile in the large?

Thanks again.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Michael Fuhr <mike (AT) fuhr (DOT) org> writes:
Yes, I think that's a fair summary.

I don't know how to solve it, and whether it would be worthwhile would
depend considerably on how expensive the proposed solution is ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Sun, Jan 16, 2005 at 04:08:35PM -0500, Tom Lane wrote:
Would the topic merit discussion in pgsql-hackers after the dust
from the 8.0 release settles down? I know little of the theory
behind query planning; I'd hate to waste the developers' time on a
topic that's already been debated or that has little merit.

If the topic is worthwhile, then I was thinking of a configuration
setting that would allow the user to request either "the plan most
likely to be the fastest" or "the plan least likely to be the slowest,"
or maybe something in between.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Michael,


On 16/1/05 12:48 AM, "Michael Fuhr" <mike (AT) fuhr (DOT) org> wrote:

I'm sorry. My mistake again while copying indexes from the log. The index
is:

CREATE UNIQUE INDEX unq_jobstat_lc_q4_2004_jobid_fetch ON jobstat_lc_q4_2004
USING btree( jobid, fetchtime);

So basically the unique index is on the same table.

Regards,
Fahad
--
main(){int j=12345;char t[]=":aAbcdefFgGhijklmnNopqrsStuUvwyz \n",
*i="dUGScUiAbpmwqbmgduAvpmmlzce\nlmGGUbFbzjdb";whi le(*i){j+=
strchr(t,*i++)-t;j%=sizeof t-1;putchar(t[j]);}return 0;}




---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)