I thought the hackers team would be interested in knowing that SourceForge, as
of Friday evening, is running on Postgres. Some 95,000 users and 12,500 Open
Source projects are depending on your stuff, so I hope it's going to be stable
for us. ;-)

Throughout the codebase we're making good use of transactions, subselects, and
foreign keys in all the places I've been wanting them for the past year, but
I'm running into some places where the query optimizer is not using the right
indexes, and sometimes does sequential scans on tables.

Here's a good example. If I remove the ORDER BY (which I didn't care to have),
postgres resorts to a sequential scan of the table, instead of using one of
3 or 4 appropriate indexes. I have an index on group_id, one on
(group_id,status_id) and one on (group_id,status_id,assigned_to) 

SELECT
bug.group_id,bug.priority,bug.bug_id,bug.summary,bug.date,users.user_name AS
submitted_by,user2.user_name AS assigned_to_user
FROM bug,users,users user2
WHERE group_id='1'
AND bug.status_id <> '3'
AND users.user_id=bug.submitted_by
AND user2.user_id=bug.assigned_to
-- 
ORDER BY bug.group_id,bug.status_id
--
LIMIT 51 OFFSET 0;

Tim

-- 
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
Sun, 10 Dec 2000 21:26:35 -0800
Tim Perdue <tim@perdue.net>
SourceForge & Postgres
Tim Perdue wrote:
> 
> I thought the hackers team would be interested in knowing that SourceForge, as
> of Friday evening, is running on Postgres. Some 95,000 users and 12,500 Open
> Source projects are depending on your stuff, so I hope it's going to be stable
> for us. ;-)
> 
> Throughout the codebase we're making good use of transactions, subselects, and
> foreign keys in all the places I've been wanting them for the past year, but
> I'm running into some places where the query optimizer is not using the right
> indexes, and sometimes does sequential scans on tables.
> 
> Here's a good example. If I remove the ORDER BY (which I didn't care to have),
> postgres resorts to a sequential scan of the table, instead of using one of
> 3 or 4 appropriate indexes. I have an index on group_id, one on
> (group_id,status_id) and one on (group_id,status_id,assigned_to)
> 
> SELECT
> bug.group_id,bug.priority,bug.bug_id,bug.summary,bug.date,users.user_name AS
> submitted_by,user2.user_name AS assigned_to_user
> FROM bug,users,users user2
> WHERE group_id='1'
> AND bug.status_id <> '3'
> AND users.user_id=bug.submitted_by
> AND user2.user_id=bug.assigned_to
> --
> ORDER BY bug.group_id,bug.status_id
> --
> LIMIT 51 OFFSET 0;

This is one of my long standing problems with Postgres, and I have
probably pissed of most of the Postgres guys with my views, but.....

Postgres is stubborn about index selection. I have a FAQ on my website.

http://www.mohawksoft.com/postgres/pgindex.html

In short, run vacuum analyze. If that doesn't fix it, it is because the
data being indexed has a lot of key fields that are probably duplicated.
Given a large table with a statistically significant number of records
assigned to a relatively few unique keys, Postgres will likely calculate
that doing a table scan is the best path.

I almost always start postmaster with the "-o -fs" switches because of
this problem.

Mon, 11 Dec 2000 21:44:26 -0500
mlw <markw@mohawksoft.com>
Re: SourceForge & Postgres
-- Start of PGP signed section.
> I thought the hackers team would be interested in knowing that SourceForge, as
> of Friday evening, is running on Postgres. Some 95,000 users and 12,500 Open
> Source projects are depending on your stuff, so I hope it's going to be stable
> for us. ;-)

This is great news.  As far as the optimizer, any chance of testing 7.1
to see if it is improved.  I believe it has been over 7.0.3.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 11 Dec 2000 22:27:37 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: SourceForge & Postgres
Bruce Momjian wrote:
> This is great news.  As far as the optimizer, any chance of testing 7.1
> to see if it is improved.  I believe it has been over 7.0.3.

I just did a test of my database that exhibits this behavior, using 7.1
from CVS.

When postmaster is started with "-o -fs" I get this:

cdinfo=# explain select * from ztitles where artistid = 0 ;
NOTICE:  QUERY PLAN:

Index Scan using ztitles_artistid_ndx on ztitles  (cost=0.00..5915.01
rows=3163 width=296)

EXPLAIN

When postmaster is started without "-o -fs" I get this:

cdinfo=# explain select * from ztitles where artistid = 0 ;
NOTICE:  QUERY PLAN:
 
Seq Scan on ztitles  (cost=0.00..4740.75 rows=3163 width=296)
 
EXPLAIN 

-- 
http://www.mohawksoft.com

Mon, 11 Dec 2000 22:54:09 -0500
mlw <markw@mohawksoft.com>
Re: SourceForge & Postgres
Tim Perdue <tim@perdue.net> writes:
> I thought the hackers team would be interested in knowing that SourceForge,
>  as of Friday evening, is running on Postgres.

Cool!

> I'm running into some places where the query optimizer is not using the right
> indexes, and sometimes does sequential scans on tables.

I assume you've done a VACUUM ANALYZE at least once since loading up
your data?

It'd be useful to see the results of an EXPLAIN for the problem query,
both with and without SET ENABLE_SEQSCAN TO OFF.  Also, it'd be helpful
to see VACUUM's stats for the relevant tables.  You can get those for
a table named 'FOO' with

select attname,attdisbursion,s.*
from pg_statistic s, pg_attribute a, pg_class c
where starelid = c.oid and attrelid = c.oid and staattnum = attnum
and relname = 'FOO';


			regards, tom lane

Mon, 11 Dec 2000 23:13:10 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: SourceForge & Postgres 
mlw <markw@mohawksoft.com> writes:
> cdinfo=# explain select * from ztitles where artistid = 0 ;
> NOTICE:  QUERY PLAN:

> Index Scan using ztitles_artistid_ndx on ztitles  (cost=0.00..5915.01
> rows=3163 width=296)

> When postmaster is started without "-o -fs" I get this:

> cdinfo=# explain select * from ztitles where artistid = 0 ;
> NOTICE:  QUERY PLAN:
 
> Seq Scan on ztitles  (cost=0.00..4740.75 rows=3163 width=296)

How many tuples are in the table?  How many are actually returned
by this query?  Also, what do you get from

select attname,attdisbursion,s.*
from pg_statistic s, pg_attribute a, pg_class c
where starelid = c.oid and attrelid = c.oid and staattnum = attnum
and relname = 'ztitles';

			regards, tom lane

Mon, 11 Dec 2000 23:19:29 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: SourceForge & Postgres 

one thing I've found to get around this is for any query that doesn't
appear to use the index properly, just do:

SET ENABLE_SEQSCAN=OFF;
<query>
SET ENABLE_SEQSCAN=ON;

that way for those queries that do work right, ou haven't forced it a
different route ..

On Mon, 11 Dec 2000, mlw wrote:

> Bruce Momjian wrote:
> > This is great news.  As far as the optimizer, any chance of testing 7.1
> > to see if it is improved.  I believe it has been over 7.0.3.
> 
> I just did a test of my database that exhibits this behavior, using 7.1
> from CVS.
> 
> When postmaster is started with "-o -fs" I get this:
> 
> cdinfo=# explain select * from ztitles where artistid = 0 ;
> NOTICE:  QUERY PLAN:
> 
> Index Scan using ztitles_artistid_ndx on ztitles  (cost=0.00..5915.01
> rows=3163 width=296)
> 
> EXPLAIN
> 
> When postmaster is started without "-o -fs" I get this:
> 
> cdinfo=# explain select * from ztitles where artistid = 0 ;
> NOTICE:  QUERY PLAN:
>  
> Seq Scan on ztitles  (cost=0.00..4740.75 rows=3163 width=296)
>  
> EXPLAIN 
> 
> -- 
> http://www.mohawksoft.com
> 

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Tue, 12 Dec 2000 00:20:00 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: SourceForge & Postgres
On Tue, Dec 12, 2000 at 12:20:00AM -0400, The Hermit Hacker wrote:
> 
> one thing I've found to get around this is for any query that doesn't
> appear to use the index properly, just do:
> 
> SET ENABLE_SEQSCAN=OFF;
> <query>
> SET ENABLE_SEQSCAN=ON;
> 
> that way for those queries that do work right, ou haven't forced it a
> different route ..

I've heard there are other ways to give clues to the
optimizer, but haven't seen anything in the docs on it. Anyway, I have gotten
virtually all of the queries optimized as much as possible. Some of the
queries are written in such a way that they key off of things in 2 or more
tables, so that's kinda hard to optimize in any circumstance.

Any plans to optimize:

-Views
-IN (1,2,3)
-SELECT count(*) FROM x WHERE indexed_field='z'

Tim

-- 
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
Mon, 11 Dec 2000 22:40:14 -0800
Tim Perdue <tim@perdue.net>
Re: SourceForge & Postgres
Tom Lane wrote:
> 
> mlw <markw@mohawksoft.com> writes:
> > cdinfo=# explain select * from ztitles where artistid = 0 ;
> > NOTICE:  QUERY PLAN:
> 
> > Index Scan using ztitles_artistid_ndx on ztitles  (cost=0.00..5915.01
> > rows=3163 width=296)
> 
> > When postmaster is started without "-o -fs" I get this:
> 
> > cdinfo=# explain select * from ztitles where artistid = 0 ;
> > NOTICE:  QUERY PLAN:
> 
> > Seq Scan on ztitles  (cost=0.00..4740.75 rows=3163 width=296)
> 
> How many tuples are in the table?  How many are actually returned
> by this query?  Also, what do you get from
> 
> select attname,attdisbursion,s.*
> from pg_statistic s, pg_attribute a, pg_class c
> where starelid = c.oid and attrelid = c.oid and staattnum = attnum
> and relname = 'ztitles';

I have attached the output. 

btw anyone trying this query should use: "attdispersion"

The explain I gave, there are no records that actually have an artistid
of 0. However, I will show the explain with a valid artistid number.

This is without "-o -fs"
cdinfo=# explain select * from ztitles where artistid = 100000220 ;
NOTICE:  QUERY PLAN:
 
Seq Scan on ztitles  (cost=0.00..4740.75 rows=3163 width=296)
 
EXPLAIN 

And this is with "-o -fs"

cdinfo=# explain select * from ztitles where artistid = 100000220 ;
NOTICE:  QUERY PLAN:
 
Index Scan using ztitles_artistid_ndx on ztitles  (cost=0.00..5915.01
rows=3163 width=296)
 
EXPLAIN


select count(*) from ztitles where artistid = 100000220 ;
 count
-------
    16
(1 row) 

-- 
http://www.mohawksoft.com
Tue, 12 Dec 2000 07:17:00 -0500
mlw <markw@mohawksoft.com>
Re: SourceForge & Postgres
mlw <markw@mohawksoft.com> writes:
> btw anyone trying this query should use: "attdispersion"

Sorry about that --- I just copied-and-pasted the query from some notes
that are obsolete as of 7.1...

> cdinfo=# explain select * from ztitles where artistid = 100000220 ;
> NOTICE:  QUERY PLAN:
 
> Seq Scan on ztitles  (cost=0.00..4740.75 rows=3163 width=296)
 
> And this is with "-o -fs"

> Index Scan using ztitles_artistid_ndx on ztitles  (cost=0.00..5915.01
> rows=3163 width=296)
 
>   attname   | attdispersion | starelid | staattnum | staop | stanullfrac | stacommonfrac |      stacommonval      |         staloval         |     stahival      
>  artistid   |     0.0477198 |    19274 |         2 |    97 |           0 |      0.149362 | 100050450              | 100000000                | 100055325

The reason why the thing is going for a sequential scan is that
astonishingly high stacommonfrac statistic.  Does artistid 100050450
really account for 14.9% of all the rows in your table?  (Who is that
anyway? ;-))  If so, a search for artistid 100050450 definitely *should*
use a sequential scan.  The problem at hand is estimating the frequency
of entries for some other artistid, given that we only have this much
statistical info available.  Obviously the stats are insufficient, and
I hope to do something about that in a release or two, but it ain't
gonna happen for 7.1.  In the meantime, if you've got huge outliers
like that, you could try reducing the value of NOT_MOST_COMMON_RATIO
in src/backend/utils/adt/selfuncs.c.

			regards, tom lane

Tue, 12 Dec 2000 16:27:15 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: SourceForge & Postgres 
Tom Lane wrote:
> 
> mlw <markw@mohawksoft.com> writes:
> > btw anyone trying this query should use: "attdispersion"
> 
> Sorry about that --- I just copied-and-pasted the query from some notes
> that are obsolete as of 7.1...
> 
> > cdinfo=# explain select * from ztitles where artistid = 100000220 ;
> > NOTICE:  QUERY PLAN:
> 
> > Seq Scan on ztitles  (cost=0.00..4740.75 rows=3163 width=296)
> 
> > And this is with "-o -fs"
> 
> > Index Scan using ztitles_artistid_ndx on ztitles  (cost=0.00..5915.01
> > rows=3163 width=296)
> 
> >   attname   | attdispersion | starelid | staattnum | staop | stanullfrac | stacommonfrac |      stacommonval      |         staloval         |     stahival
> >  artistid   |     0.0477198 |    19274 |         2 |    97 |           0 |      0.149362 | 100050450              | 100000000                | 100055325
> 
> The reason why the thing is going for a sequential scan is that
> astonishingly high stacommonfrac statistic.  Does artistid 100050450
> really account for 14.9% of all the rows in your table?  (Who is that
> anyway? ;-))  If so, a search for artistid 100050450 definitely *should*
> use a sequential scan.

I tested this statement against the database and you are right, about 14
seconds with the index, 4 without.

BTW ID # 100050450 is "Various Artists"

This is sort of a point I was trying to make in previous emails. I think
this situation, and this sort of ratio is far more likely than the
attention it has been given.

In about every project I have used postgres I have run into this. It is
only recently that I have understood what the problem was and how to get
around it (sort of).

This one entry is destroying any intelligent performance we could hope
to attain. As I said, I always see this sort of behavior in some
implementation.


>  The problem at hand is estimating the frequency
> of entries for some other artistid, given that we only have this much
> statistical info available.  Obviously the stats are insufficient, and
> I hope to do something about that in a release or two, but it ain't
> gonna happen for 7.1.  In the meantime, if you've got huge outliers
> like that, you could try reducing the value of NOT_MOST_COMMON_RATIO
> in src/backend/utils/adt/selfuncs.c.

I did some playing with this value, and I can seem to have it
differentiate between 100050450 and anything else.


-- 
http://www.mohawksoft.com

Tue, 12 Dec 2000 19:20:03 -0500
mlw <markw@mohawksoft.com>
Re: SourceForge & Postgres

Tim, how is PostgreSQL working for you?

-- Start of PGP signed section.
> I thought the hackers team would be interested in knowing that SourceForge, as
> of Friday evening, is running on Postgres. Some 95,000 users and 12,500 Open
> Source projects are depending on your stuff, so I hope it's going to be stable
> for us. ;-)
> 
> Throughout the codebase we're making good use of transactions, subselects, and
> foreign keys in all the places I've been wanting them for the past year, but
> I'm running into some places where the query optimizer is not using the right
> indexes, and sometimes does sequential scans on tables.
> 
> Here's a good example. If I remove the ORDER BY (which I didn't care to have),
> postgres resorts to a sequential scan of the table, instead of using one of
> 3 or 4 appropriate indexes. I have an index on group_id, one on
> (group_id,status_id) and one on (group_id,status_id,assigned_to) 
> 
> SELECT
> bug.group_id,bug.priority,bug.bug_id,bug.summary,bug.date,users.user_name AS
> submitted_by,user2.user_name AS assigned_to_user
> FROM bug,users,users user2
> WHERE group_id='1'
> AND bug.status_id <> '3'
> AND users.user_id=bug.submitted_by
> AND user2.user_id=bug.assigned_to
> -- 
> ORDER BY bug.group_id,bug.status_id
> --
> LIMIT 51 OFFSET 0;
> 
> Tim
> 
> -- 
> Founder - PHPBuilder.com / Geocrawler.com
> Lead Developer - SourceForge
> VA Linux Systems
-- End of PGP section, PGP failed!


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 22 Jan 2001 23:05:47 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: SourceForge & Postgres
Tim Perdue wrote:
> I thought the hackers team would be interested in knowing that SourceForge, as
> of Friday evening, is running on Postgres. Some 95,000 users and 12,500 Open
> Source projects are depending on your stuff, so I hope it's going to be stable
> for us. ;-)

Tim,

    the  PG  core team is wondering if SourceForge might still be
    running on a snapshot prior to  BETA3,  because  there  is  a
    major bug in it that could result in a complete corruption of
    the system catalog.

    The bug is that the shared buffer cache might mix  up  blocks
    between  different  databases.  As  long  as you only use one
    database, you're fairly safe.  But  a  single  'createdb'  or
    'createuser'  on  the  same  instance, which is connecting to
    template1, could blow away your entire  installation.  It  is
    fixed in BETA3.

    My personal recommendation should be clear.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Thu, 25 Jan 2001 16:51:44 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: SourceForge & Postgres
Tim,

I've found your message in postgres hackers list and wondering if
sourceforge db part could be improved using our recent (7.1) GiST improvements.

In short, using RD-Tree + GiST we've added index support for arrays of
integers. For example, in our rather busy web site we have pool
of online news. Most complex query to construct main page is
select messages from given list of categories, because it requires
join from  message_section_map (message could belong to several
categories).
messages    message_section_map
--------    -------------------
msg_id      msg_id
title       sect_id
.....

WHERE clause (simplificated) looks like
......
message_section_map.sect_id in (1,13,103,10488,105,17,9,4,2,260000373,12,7,8,14,5,6,11,15,
10339,10338,10336,10335,260000404,260000405,260000403,206) and
message_section_map.msg_id = messages.msg_id order by publication_date
desc .....

This is really difficult query and takes a long time to execute.

now, we exclude message_section_map, just add array <sections> to
table messages which contains all sect_id given message belong to.
Using our index support for arrays of int4  our complex query
executes very fast !

I think sourceforge uses some kind of such queries.

Some info about GiST extension and our contribution could be find
at http://www.sai.msu.su/~megera/postgres/gist/


	Regards,
		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83


Sat, 10 Feb 2001 18:08:54 +0300 (GMT)
Oleg Bartunov <oleg@sai.msu.su>
Re: SourceForge & Postgres