Re: [PERFORM] Strange result: UNIX vs. TCP/IP sockets
Hi all, You may remember in my last report, I said that it appeared that TCP/IP connections caused EXPLAIN ANALYSE to return (repeatably but not consistently) slower times than when connected over UNIX domain sockets. This turns out to be false. We (well, Chris Browne, actually) ran some tests which demonstrated that the performance problem turned up over the UNIX socket, as well. It was just a statistical fluke that our smaller sample always found the problem on TCP/IP. Of course, now we have some other work to do, but we can rule out the transport at least. Chalk one up for sane results. If we discover any more, I'll post it here. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Efficiency of timestamps
On Tue, 8 Jul 2003, Martin Foster wrote:
As I keep looking through code to see where I can make things more
efficient, I noticed that in some cases timestamps seem horribly
inefficient. This leads to very long run times for certain queries.
Here is an example:
-- USING TIMESTAMPS TO NARROW DOWN --
SELECT
Post.PostIDNumber,
Post.PuppeteerLogin,
Post.PuppetName,
Post.PostCmd,
Post.PostClass
FROM ethereal.Post
WHERE Post.PostTimeStamp (LOCALTIMESTAMP - INTERVAL '10 Minutes')
AND Post.RealmName='Amalgam'
AND (Post.PostTo='all' OR Post.PostTo='root')
AND (NOT EXISTS (SELECT PuppetIgnore.PuppetLogin
FROM ethereal.PuppetIgnore
WHERE PuppetIgnore.PuppetIgnore='global'
AND PuppetIgnore.PuppeteerLogin='root'
AND PuppetIgnore.PuppetLogin=Post.PuppeteerLogin)
OR Post.PuppeteerLogin IS NULL)
AND (NOT EXISTS (SELECT PuppetIgnore.PuppetName
FROM ethereal.PuppetIgnore
WHERE PuppetIgnore='single'
AND PuppetIgnore.PuppeteerLogin='root'
AND PuppetIgnore.PuppetName=Post.PuppetName)
OR Post.PuppetName IS NULL)
ORDER BY Post.PostIDNumber LIMIT 100
-- Explain of Above--
Limit (cost=0.00..260237.32 rows=100 width=48)
- Index Scan using pkpost on post (cost=0.00..3020594.00 rows=1161
width=48)
Filter: ((posttimestamp (('now'::text)::timestamp(6) without
time zone - '00:10'::interval)) AND (realmname = 'Amalgam'::character
varying) AND ((postto = 'all'::character varying) OR (postto =
'root'::character varying)) AND ((NOT (subplan)) OR (puppeteerlogin IS
NULL)) AND ((NOT (subplan)) OR (puppetname IS NULL)))
I think you might get better results with some kind of multi-column index.
It's using the index to avoid a sort it looks like, but it's not helping
to find the conditions. I can't remember the correct ordering, but maybe
(posttimestamp, realmname, postidnumber). Having separate indexes on the
fields won't help currently since only one index will get chosen for the
scan. Also, what does explain analyze show?
-- NAME: Post
-- REFERENCES : Realm*
-- Puppet*
-- PuppeteerLogin*
--
-- DESCRIPTION : Post is the hive of activity for all realms.
Associated with all three
-- major tables, it is not actually linked because of the
nature of storing
-- posts for statistics and auditing.
CREATE TABLE ethereal.Post (
PostIDNumber INT NOT NULL DEFAULT
NEXTVAL('ethereal.seqPost'),
RealmNameVARCHAR(30) NOT NULL,
PuppetName VARCHAR(30),
PuppeteerLogin VARCHAR(10),
PostTo VARCHAR(30),
PostTimestampTIMESTAMP NOT NULL DEFAULT LOCALTIMESTAMP,
PostClassVARCHAR(10) NOT NULL DEFAULT 'general',
PostCmd VARCHAR(10) NOT NULL DEFAULT 'none',
PostFullFormat TEXT,
PostImagelessFormat TEXT,
PostPartialFormatTEXT,
CONSTRAINT pkPost PRIMARY KEY (PostIDNumber),
CONSTRAINT enumPostClass CHECK (PostCLass IN
('banner','dice','duplicate','general','play','private','special','system')),
CONSTRAINT enumPostCmd CHECK (PostCmd IN
('general','none','play','stream'))
) WITHOUT OIDS;
-- STANDARD INDEX
CREATE INDEX idxPost_Class ON ethereal.Post
(
PostClass
);
CREATE INDEX idxPost_Login ON ethereal.Post
(
PuppeteerLogin
);
CREATE INDEX idxPost_Puppet ON ethereal.Post
(
PuppetName
);
CREATE INDEX idxPost_Realm ON ethereal.Post
(
RealmName
);
CREATE INDEX idxPost_Timestamp ON ethereal.Post
(
PostTimestamp
);
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Efficiency of timestamps
On Tue, 8 Jul 2003, Martin Foster wrote:
Stephan Szabo wrote:
I think you might get better results with some kind of multi-column index.
It's using the index to avoid a sort it looks like, but it's not helping
to find the conditions. I can't remember the correct ordering, but maybe
(posttimestamp, realmname, postidnumber). Having separate indexes on the
fields won't help currently since only one index will get chosen for the
scan. Also, what does explain analyze show?
Hope that shed's light on the matter.
Limit (cost=0.00..260237.32 rows=100 width=48) (actual
time=68810.26..68820.83 rows=55 loops=1)
- Index Scan using pkpost on post (cost=0.00..3020594.00
rows=1161 width=48) (actual time=68810.25..68820.72 rows=55 loops=1)
Filter: ((posttimestamp (('now'::text)::timestamp(6) without
time zone - '00:10'::interval)) AND (realmname = 'Amalgam'::character
varying) AND ((postto = 'all'::character varying) OR (postto =
'root'::character varying)) AND ((NOT (subplan)) OR (puppeteerlogin IS
NULL)) AND ((NOT (subplan)) OR (puppetname IS NULL)))
SubPlan
- Index Scan using pkpuppetignore on puppetignore
(cost=0.00..13.31 rows=1 width=10) (actual time=0.02..0.02 rows=0 loops=55)
Index Cond: (puppeteerlogin = 'root'::character varying)
Filter: ((puppetignore = 'global'::character varying)
AND (puppetlogin = $0))
- Index Scan using pkpuppetignore on puppetignore
(cost=0.00..5.84 rows=1 width=15) (actual time=0.01..0.01 rows=0 loops=55)
Index Cond: ((puppeteerlogin = 'root'::character
varying) AND (puppetname = $1))
Filter: (puppetignore = 'single'::character varying)
Total runtime: 68821.11 msec
The row estimate is high. How many rows meet the various conditions and
some of the combinations? And how many rows does it estimate if you do a
simpler query on those with explain?
I still think some variety of multi-column index to make the above index
conditions would help, but you'd probably need to play with which ones
help, and with the cost cut for the limit, I don't know if it'd actually
get a better plan, but it may be worth trying a bunch and seeing which
ones are useful and then dropping the rest.
---(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
