Re: [PERFORM] Unique index and estimated rows.
Uh oh,
function indexes seem to be a bit crippled. I created a unique index
without the upper() function and number of estimated rows is now just
right.
users_nick unique, btree (nick)
And the plan:
galleria= explain analyze SELECT i.image_id, i.info, i.stamp, i.status, i.t_width,
i.t_height, u.nick, u.uid FROM image i, users u WHERE i.uid = u.uid AND nick =
'FireGirl-' AND i.status IN ('d', 'v') AND u.status = 'a' ORDER BY status, stamp DESC;
QUERY PLAN
---
Sort (cost=154.10..154.12 rows=7 width=63) (actual time=0.227..0.237 rows=19 loops=1)
Sort Key: i.status, i.stamp
- Nested Loop (cost=0.00..154.00 rows=7 width=63) (actual time=0.075..0.176
rows=19 loops=1)
- Index Scan using users_nick on users u (cost=0.00..6.01 rows=1 width=14)
(actual time=0.041..0.043 rows=1 loops=1)
Index Cond: ((nick)::text = 'FireGirl-'::text)
Filter: (status = 'a'::bpchar)
- Index Scan using image_uid_status on image i (cost=0.00..147.73 rows=21
width=53) (actual time=0.026..0.079 rows=19 loops=1)
Index Cond: (i.uid = outer.uid)
Filter: ((status = 'd'::bpchar) OR (status = 'v'::bpchar))
Total runtime: 0.303 ms
(10 rows)
I think that creating an uppercase column for name and unique index for
could be a workaround for this.
Another problem is that function indexes don't seem to care about
statistics target settings.
|\__/|
( oo )Kari Lavikka - [EMAIL PROTECTED] - (050) 380 3808
__ooO( )Ooo___ _ ___ _ _ _ __ _ _
On Fri, 30 Jan 2004, Kari Lavikka wrote:
Hi, more strange plans ...
Planner estimates an indexscan to return 240 rows although it is using a
unique index and chooses to use hash join and seqscan instead of nested
loop and indexscan. It's ... very slow.
Idexes used:
users: users_upper_nick unique, btree (upper((nick)::text))
image: image_uid_status btree (uid, status)
galleria= set enable_hashjoin = true;
SET
galleria= explain analyze SELECT i.image_id, i.info, i.stamp, i.status, i.t_width,
i.t_height, u.nick, u.uid FROM image i, users u WHERE i.uid = u.uid AND
upper(u.nick) = upper('FireGirl-') AND i.status IN ('d', 'v') AND u.status = 'a'
ORDER BY status, stamp DESC;
QUERY PLAN
-
Sort (cost=24731.07..24734.95 rows=1550 width=63) (actual
time=1392.675..1392.686 rows=19 loops=1)
Sort Key: i.status, i.stamp
- Hash Join (cost=961.31..24648.94 rows=1550 width=63) (actual
time=552.184..1392.617 rows=19 loops=1)
Hash Cond: (outer.uid = inner.uid)
- Seq Scan on image i (cost=0.00..22025.22 rows=329382 width=53) (actual
time=0.009..1088.856 rows=346313 loops=1)
Filter: ((status = 'd'::bpchar) OR (status = 'v'::bpchar))
- Hash (cost=960.71..960.71 rows=240 width=14) (actual time=0.043..0.043
rows=0 loops=1)
- Index Scan using users_upper_nick on users u (cost=0.00..960.71
rows=240 width=14) (actual time=0.037..0.039 rows=1 loops=1)
Index Cond: (upper((nick)::text) = 'FIREGIRL-'::text)
Filter: (status = 'a'::bpchar)
Total runtime: 1392.769 ms
(11 rows)
galleria= set enable_hashjoin = false;
SET
galleria= explain analyze SELECT i.image_id, i.info, i.stamp, i.status, i.t_width,
i.t_height, u.nick, u.uid FROM image i, users u WHERE i.uid = u.uid AND
upper(u.nick) = upper('FireGirl-') AND i.status IN ('d', 'v') AND u.status = 'a'
ORDER BY status, stamp DESC;
QUERY PLAN
---
Sort (cost=35861.87..35865.74 rows=1550 width=63) (actual
time=0.230..0.244 rows=19 loops=1)
Sort Key: i.status, i.stamp
- Nested Loop (cost=0.00..35779.73 rows=1550 width=63) (actual
time=0.070..0.173 rows=19 loops=1)
- Index Scan using users_upper_nick on users u (cost=0.00..960.71
rows=240 width=14) (actual time=0.036..0.038 rows=1 loops=1)
Index Cond: (upper((nick)::text) = 'FIREGIRL-'::text)
Filter: (status = 'a'::bpchar)
- Index Scan using image_uid_status on image i (cost=0.00..144.83
rows=20 width=53) (actual time=0.026..0.080 rows=19 loops=1)
Index Cond: (i.uid = outer.uid)
Filter: ((status = 'd'::bpchar) OR (status = 'v'::bpchar))
Total runtime: 0.315 ms
(10 rows)
|\__/|
( oo )Kari Lavikka -
Re: [PERFORM] another query optimization question
Hi Tim, you are right: Interesting. I have recollected where we saw this before: http://archives.postgresql.org/pgsql-hackers/2003-11/msg01528.php Apparently gettimeofday() has a glitch on some BSD releases. OS X is a BSD derivative and it's not so surprising if it has it too. May I suggest that you try the test program given here: http://archives.postgresql.org/pgsql-hackers/2003-11/msg01546.php and file a bug report with Apple if it shows any out-of-order results? I am fairly certain that I tried that test program when I devised it on my own OS X machine, and couldn't get a failure. Maybe it depends on your hardware (hm, could dual CPUs be the key factor)? p:~ david$ ./a.out bogus tv_usec: 1075544305 -615731632, prev 1075544304 349672 out of order tv_sec: 1075544304 349759, prev 1075544305 -615731632 out of order tv_usec: 1075544305 339904, prev 1075544305 339905 bogus tv_usec: 1075544307 -615731811, prev 1075544306 349493 out of order tv_sec: 1075544306 349498, prev 1075544307 -615731811 out of order tv_usec: 1075544307 339442, prev 1075544307 339443 out of order tv_usec: 1075544308 339351, prev 1075544308 339352 This is a part of the output. Whats -very- interesting: Apple provides a little tool that can enable / disable the l2 cache ... one CPU of a dual CPU system on the fly. When i start the testapp with two CPU's enabled i get this output here, when i turn off one CPU while the app is still running the messages disappear as long as one CPU is turned off. Reactivating the CPU again produces new error messages. I checked the app on a single G4, no errors and i checked the app on a dual G4, -not- G5 and also no error messages. Do you remember where one can find a patch? Maybe its something one can fix because parts of the OS from Apple are 'open source'. Do you know if this bug makes a system unusable with PostgresSQL? Regards David ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] High Performance/High Reliability File system on SuSE64
Christopher Weimann wrote: On 01/23/2004-10:18AM, Joshua D. Drake wrote: XFS also has the interesting ability (although I have yet to test it) that will allow you to take a snapshot of the filesystem. Thus you can have filesystem level backups of the PGDATA directory that are consistent even though the database is running. You can do snapshots in FreeBSD 5.x with UFS2 as well but that ( nor XFS snapshots ) will let you backup with the database server running. Just because you will get the file exactly as it was at a particular instant does not mean that the postmaster did not still have some some data that was not flushed to disk yet. Uh, yea, it does. If the snapshot includes all of /data, including WAL/xlog, you can then back up the snapshot and restore it on another machine. It will restart just like a crash. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] another query optimization question
On Sat, 31 Jan 2004, Tom Lane wrote: David Teran [EMAIL PROTECTED] writes: Apple provides a little tool that can enable / disable the l2 cache ... one CPU of a dual CPU system on the fly. When i start the testapp with two CPU's enabled i get this output here, when i turn off one CPU while the app is still running the messages disappear as long as one CPU is turned off. Reactivating the CPU again produces new error messages. Ah-hah, so the gettimeofday bug *is* linked to multiple CPUs. Marc, were the machines you saw it on all multi-CPU? I'm not sure ... I thought I ran it on my P4 here in the office and saw it too, albeit not near as frequently ... but, in FreeBSD's case, it is a design issue ... there are two different functions, once that is kinda fuzzy (but fast), and the other that is designed to be exact, but at a performance loss ... or was it the same function, but a 'sysctl' variable that changes the state? Can't remember which, but it is by design on FreeBSD ... and, if we're talking about Apple, the same most likely applies, as its based on the same kernel ... Back of my mind, I *think* it was these sysctl variables: kern.timecounter.method: 0 kern.timecounter.hardware: i8254 Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] another query optimization question
Hi, I'm not sure ... I thought I ran it on my P4 here in the office and saw it too, albeit not near as frequently ... but, in FreeBSD's case, it is a design issue ... there are two different functions, once that is kinda fuzzy (but fast), and the other that is designed to be exact, but at a performance loss ... or was it the same function, but a 'sysctl' variable that changes the state? Can't remember which, but it is by design on FreeBSD ... and, if we're talking about Apple, the same most likely applies, as its based on the same kernel ... Back of my mind, I *think* it was these sysctl variables: kern.timecounter.method: 0 kern.timecounter.hardware: i8254 I will try to check this on my system. But here another hint, maybe more interesting for Apple though: The bug does -not- occur if another process uses a lot of CPU time. We encoded a quicktime movie into mpeg2 and while this was using about 90% and while encoding the vcd i wanted to show the bug to a friend and it did not work. But besides this, is there any chance that we can optimize our initial performance problem ;-) regards David ---(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
