Thread

1. Efficiency of timestamps

   Martin Foster <martin@ethereal-realms.org> — 2003-07-09T00:27:36Z

   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)))
            SubPlan
              ->  Index Scan using pkpuppetignore on puppetignore 
   (cost=0.00..13.31 rows=1 width=10)
                    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)
                    Index Cond: ((puppeteerlogin = 'root'::character 
   varying) AND (puppetname = $1))
                    Filter: (puppetignore = 'single'::character varying)
   
   
   Result : 22 rows fetched (17.21 sec)
   
   
   -- USING A GENERATED ID NUMBER --
   
   SELECT
     Post.PostIDNumber,
     Post.PuppeteerLogin,
     Post.PuppetName,
     Post.PostCmd,
     Post.PostClass
     FROM ethereal.Post
     WHERE Post.PostIDNumber > 1
     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..86712.10 rows=100 width=48)
      ->  Index Scan using pkpost on post  (cost=0.00..3019119.56 rows=3482 
   width=48)
            Index Cond: (postidnumber > 1)
            Filter: ((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)
                    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)
                    Index Cond: ((puppeteerlogin = 'root'::character 
   varying) AND (puppetname = $1))
                    Filter: (puppetignore = 'single'::character varying)
   
   
   Result : 100 rows fetched ( 0.19 sec)
   
   
   -- USING A MIXTURE OF BOTH --
   
   SELECT
     Post.PostIDNumber,
     Post.PuppeteerLogin,
     Post.PuppetName,
     Post.PostCmd,
     Post.PostClass
     FROM ethereal.Post
     WHERE Post.PostIDNumber > (SELECT MIN(PostIDNumber)
     FROM ethereal.Post
     WHERE Post.PostTimeStamp > (LOCALTIMESTAMP - INTERVAL '10 minutes'))::INT
     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..87101.38 rows=100 width=48)
      InitPlan
        ->  Aggregate  (cost=12412.82..12412.82 rows=1 width=4)
              ->  Index Scan using idxpost_timestamp on post 
   (cost=0.00..12282.42 rows=52160 width=4)
                    Index Cond: (posttimestamp > 
   (('now'::text)::timestamp(6) without time zone - '00:10'::interval))
      ->  Index Scan using pkpost on post  (cost=0.00..1010992.25 rows=1161 
   width=48)
            Index Cond: (postidnumber > $0)
            Filter: ((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)
                    Index Cond: (puppeteerlogin = 'root'::character varying)
                    Filter: ((puppetignore = 'global'::character varying) 
   AND (puppetlogin = $1))
              ->  Index Scan using pkpuppetignore on puppetignore 
   (cost=0.00..5.84 rows=1 width=15)
                    Index Cond: ((puppeteerlogin = 'root'::character 
   varying) AND (puppetname = $2))
                    Filter: (puppetignore = 'single'::character varying)
   
   
   Result : 18 rows fetched ( 0.04 sec)
   
   
   Both PostIDNumber and PostTimestamp are indexed, so that should not be a 
   bottleneck in itself.    However, as you can see in the third example 
   the use of a sub-query actually accelerates the process considerably, 
   meaning that integer based searching is much much faster.
   
   Under MySQL timestamps where in Unix time, which is why I may have never 
   noticed such an extreme slowdown when doing similar on that script.   Of 
   course to boggle the mind, here is a view that works very well:
   
   CREATE VIEW ethereal.Who AS
      SELECT
        Po.PuppetName     AS PuppetName,
        Po.PuppeteerLogin AS PuppeteerLogin,
        Po.RealmName      AS RealmName,
        Re.RealmPublic    AS RealmPublic,
        Re.RealmVerified  AS RealmVerified
      FROM ethereal.Post Po, ethereal.Puppet Ch, ethereal.Realm Re
      WHERE Po.PuppeteerLogin = Ch.PuppeteerLogin
        AND Po.RealmName = Re.RealmName
        AND Po.PostTimestamp > (LOCALTIMESTAMP - INTERVAL '10 minutes')
        AND Po.PuppetName IS NOT NULL
      GROUP BY Po.PuppeteerLogin, Po.PuppetName, Po.RealmName, 
   Re.RealmPublic, Re.RealmVerified
      ORDER BY Po.RealmName, Po.PuppetName;
   
   Sort  (cost=309259.89..309629.34 rows=147780 width=79)
      Sort Key: po.realmname, po.puppetname
      ->  Group  (cost=270648.27..292815.19 rows=147780 width=79)
            ->  Sort  (cost=270648.27..274342.75 rows=1477795 width=79)
                  Sort Key: po.puppeteerlogin, po.puppetname, po.realmname, 
   re.realmpublic, re.realmverified
                  ->  Merge Join  (cost=22181.60..41087.65 rows=1477795 
   width=79)
                        Merge Cond: ("outer".puppeteerlogin = 
   "inner".puppeteerlogin)
                        ->  Sort  (cost=17172.82..17300.26 rows=50978 width=69)
                              Sort Key: po.puppeteerlogin
                              ->  Hash Join  (cost=12.41..13186.95 
   rows=50978 width=69)
                                    Hash Cond: ("outer".realmname = 
   "inner".realmname)
                                    ->  Index Scan using idxpost_timestamp 
   on post po  (cost=0.00..12282.42 rows=50978 width=42)
                                          Index Cond: (posttimestamp > 
   (('now'::text)::timestamp(6) without time zone - '00:10'::interval))
                                          Filter: (puppetname IS NOT NULL)
                                    ->  Hash  (cost=11.93..11.93 rows=193 
   width=27)
                                          ->  Seq Scan on realm re 
   (cost=0.00..11.93 rows=193 width=27)
                        ->  Sort  (cost=5008.78..5100.22 rows=36574 width=10)
                              Sort Key: ch.puppeteerlogin
                              ->  Seq Scan on puppet ch 
   (cost=0.00..2236.74 rows=36574 width=10)
   
   
   Result : 48 rows fetched ( 0.55 sec)
   
   
   It uses the exact same time restraint as the first three examples, looks 
   through the same table, does a tipple join and still gets off at higher 
   speeds.   This seems to indicate that timestamps are actually efficient, 
   which contradicts above examples.
   
   Any ideas?   Code for the table creation is below signature:
   
   	Martin Foster
   	Creator/Designer Ethereal Realms
   	martin@ethereal-realms.org
   
   
   --
   --
   -- 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'),
      RealmName            VARCHAR(30)   NOT NULL,
      PuppetName           VARCHAR(30),
      PuppeteerLogin       VARCHAR(10),
      PostTo               VARCHAR(30),
      PostTimestamp        TIMESTAMP     NOT NULL DEFAULT LOCALTIMESTAMP,
      PostClass            VARCHAR(10)   NOT NULL DEFAULT 'general',
      PostCmd              VARCHAR(10)   NOT NULL DEFAULT 'none',
      PostFullFormat       TEXT,
      PostImagelessFormat  TEXT,
      PostPartialFormat    TEXT,
      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
   );
   
   
   
   

2. Re: Efficiency of timestamps

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-07-09T00:55:51Z

   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'),
   >    RealmName            VARCHAR(30)   NOT NULL,
   >    PuppetName           VARCHAR(30),
   >    PuppeteerLogin       VARCHAR(10),
   >    PostTo               VARCHAR(30),
   >    PostTimestamp        TIMESTAMP     NOT NULL DEFAULT LOCALTIMESTAMP,
   >    PostClass            VARCHAR(10)   NOT NULL DEFAULT 'general',
   >    PostCmd              VARCHAR(10)   NOT NULL DEFAULT 'none',
   >    PostFullFormat       TEXT,
   >    PostImagelessFormat  TEXT,
   >    PostPartialFormat    TEXT,
   >    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
   >
   
   
   
   

3. Re: Efficiency of timestamps

   Martin Foster <martin@ethereal-realms.org> — 2003-07-09T01:37:16Z

   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
   
   -- 
   	Martin Foster
   	Creator/Designer Ethereal Realms
   	martin@ethereal-realms.org
   
   
   
   

4. Re: Efficiency of timestamps

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-07-09T02:49:04Z

   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.
   
   
   
   

5. Re: Efficiency of timestamps

   Martin Foster <martin@ethereal-realms.org> — 2003-07-09T04:23:38Z

   Stephan Szabo wrote:
   > 
   > 
   > 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.
   > 
   > 
   
   At any given point in time you would not expect to see much more then 30 
   posts applying for a time based search.    That is primarily a result of 
   having more then one room for which posts are attached to, and then some 
   posts exist just to show people are there et cetera.
   
   Simpler queries seem to do quiet well.   That view makes use of the same 
   table and seems to have no performance impact from doing as such, and 
   the position based search is considerably faster.
   
   I can show EXPLAIN ANALYSE for all of those if you wish.
   
   	Martin Foster
   	Creator/Designer Ethereal Realms
   	martin@ethereal-realms.org
   
   
   
   

6. Re: Efficiency of timestamps

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-07-09T05:01:33Z

   On Tue, 8 Jul 2003, Martin Foster wrote:
   
   > Stephan Szabo wrote:
   >
   > > 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.
   > >
   > At any given point in time you would not expect to see much more then 30
   > posts applying for a time based search.    That is primarily a result of
   > having more then one room for which posts are attached to, and then some
   > posts exist just to show people are there et cetera.
   >
   > Simpler queries seem to do quiet well.   That view makes use of the same
   > table and seems to have no performance impact from doing as such, and
   > the position based search is considerably faster.
   
   Well, the reason I asked is to see both whether the estimates for the
   various columns were somewhere near reality (if not, then you may need to
   raise the statistics target for the column) which might affect whether
   it'd consider using a multi-column index for the conditions and sort
   rather than the index scan it was using.
   
   
   

7. Re: Efficiency of timestamps

   Martin Foster <martin@ethereal-realms.org> — 2003-07-09T06:51:42Z

   Stephan Szabo wrote:
   > 
   > Well, the reason I asked is to see both whether the estimates for the
   > various columns were somewhere near reality (if not, then you may need to
   > raise the statistics target for the column) which might affect whether
   > it'd consider using a multi-column index for the conditions and sort
   > rather than the index scan it was using.
   > 
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 4: Don't 'kill -9' the postmaster
   
   I'm going to have to pull out the 'Practical PostgreSQL' book and brush 
   up on optimizing.   This level of optimization is not something I have 
   had to deal with in the past.
   
   Also to make this interesting.  The sub-query method is faster at times 
   and slower in others.   But doing two separate queries and working on 
   the PostIDNumber field exclusively is always blazingly fast...
   
        Martin Foster
        Creator/Designer Ethereal Realms
        martin@ethereal-realms.org