Thread: Postmaster response
Hello. I am using PostgreSQL version 7.0.2 with RedHat 6.1. When I start up the postmaster at the prompt, I get the following response and the session hangs and doesn't return to the prompt but I am able to use my databases and PostgreSQL. Is this normal or correct? If not, what should I do to fix this problem? [postgres@cd480405-a sean]$ /usr/bin/postmaster -D /var/lib/pgsql 000714.09:14:23.824 [2788] DEBUG: Data Base System is starting up at Fri Jul 14 09:14:23 2000 000714.09:14:23.825 [2788] DEBUG: Data Base System was shut down at Fri Jul 14 09:13:55 2000 000714.09:14:23.828 [2788] DEBUG: Data Base System is in production state at Fri Jul 14 09:14:23 2000 Thank you for your time. Sean.
"Sean Alphonse" <salphonse1@home.com> writes: > I am using PostgreSQL version 7.0.2 with RedHat 6.1. When I start up the > postmaster at the prompt, I get the following response and the session hangs > and doesn't return to the prompt but I am able to use my databases and > PostgreSQL. Is this normal or correct? It is if you start the postmaster like that --- but no one would do so except for testing purposes. See the Administrator's Guide, especially http://www.postgresql.org/docs/postgres/postmaster-start.htm regards, tom lane
We have a dB with one table having about 1.3 million rows. We want to be
able to search subsets of the data. There are about 15 subsets and their
members are (more or less) fixed.
Assume we have the table 'main' with columns 'stockno' and 'descrip' and an
index on stockno. We create a subset table with a single column 'stockno',
populate it with:
insert into subset_table select stockno from main where <SOME CONDITION>;
and indexed it with
create index sub_idx on subset_table(stockno);
Now we want to select all entries in main that are in the subset also in
subset_table. So we do
select main.* from main m, subset_table s where m.stockno=s.stockno and
m.descrip ~ 'SEARCHTERM';
EXPLAIN show that main is first searched for SEARCHTERM then the results
filtered for s.stockno=m.stockno.
We would like it the other way round. I.e. only do the ~ match on descrip
for those rows in the subset.
How can we achieve this?
Thanks
Steve
Nested Loop (cost=317205.16 rows=3074115 width=184)
-> Nested Loop (cost=79304.37 rows=3 width=24)
-> Seq Scan on main m (cost=79300.27 rows=2 width=12)
-> Index Scan using sub_idx on subset_table s (cost=2.05
rows=203793 width=12)
-> Seq Scan on main (cost=79300.27 rows=1024705 width=160)
--
thorNET - Internet Consultancy, Services & Training
Phone: 01454 854413
Fax: 01454 854412
http://www.thornet.co.uk
At 11:44 17/07/00 -0700, Stephan Szabo wrote:
>First off, is that query really what you want?
>main.* is probably adding an extra join with main (see the explain output --
>it appears to be doing two joins against main, one as m and one as main).
>
>Try the query as
>select m.* from main m, subset_table s where m.stockno=s.stockno and
>m.descrip ~ 'SEARCHTERM';
>
>and see what it gives you then.
>
Its different, but it still does the 'wrong' scan first and even stranger
now it doesnt do an indexed scan on subset_table:
explain select m.* from main m, subset_table s
where m.stockno=s.stockno AND m.descrip ~ 'SEARCHTERM';
NOTICE: QUERY PLAN:
Hash Join (cost=118431.88 rows=714797 width=172)
-> Seq Scan on main m (cost=79300.27 rows=714796 width=160)
-> Hash (cost=8121.17 rows=203793 width=12)
-> Seq Scan on subset_table s (cost=8121.17 rows=203793 width=12)
I'm confused !
Steve
--
thorNET - Internet Consultancy, Services & Training
Phone: 01454 854413
Fax: 01454 854412
http://www.thornet.co.uk
Steve Heaven <steve@thornet.co.uk> writes:
> explain select m.* from main m, subset_table s
> where m.stockno=s.stockno AND m.descrip ~ 'SEARCHTERM';
> NOTICE: QUERY PLAN:
> Hash Join (cost=118431.88 rows=714797 width=172)
> -> Seq Scan on main m (cost=79300.27 rows=714796 width=160)
> -> Hash (cost=8121.17 rows=203793 width=12)
> -> Seq Scan on subset_table s (cost=8121.17 rows=203793 width=12)
Given the sizes of the tables, I'm not sure that's such an unreasonable
plan. Your subset seems to be more than a quarter of the rows in the
main table. The only way to build a plan that visits *only* those rows
in main that are also in subset is to use an indexscan on main, right?
The cold hard fact is that an indexscan that touches a quarter of the
rows in a large table is going to be slower than a linear scan of the
whole table. (Barring special cases such as a nearly-in-order table,
anyway.) The cost of extra disk I/O will swamp out the extra
comparisons.
So I think the planner is doing the right thing to use a seqscan on
main, and then its only interesting choice is how to perform the join
with subset. The above looks pretty good, bearing in mind again that
an indexscan that has to touch every row in subset will be mighty slow.
My advice is to forget about the subset table; it's not buying you
anything that's worth the trouble of doing the join. If your original
subset condition is really expensive, you could run it and store the
result in a field added to the main table --- otherwise, why not just
evaluate it on the fly? In any case, retrieving a subset that
represents more than a few percent of the total number of rows is
likely best done by a sequential scan, unsexy as that may sound.
BTW, it would be interesting to see if the 7.0.* planner produces a
materially different plan than the pre-7.0 version I observe you
are using. Its cost estimates will be different, but I expect it
will arrive at the same conclusion that indexscan is not the way
to do this query.
regards, tom lane
On Tue, 18 Jul 2000, Steve Heaven wrote: > At 11:44 17/07/00 -0700, Stephan Szabo wrote: > >First off, is that query really what you want? > >main.* is probably adding an extra join with main (see the explain output -- > >it appears to be doing two joins against main, one as m and one as main). > > > >Try the query as > >select m.* from main m, subset_table s where m.stockno=s.stockno and > >m.descrip ~ 'SEARCHTERM'; > > > >and see what it gives you then. > > > > Its different, but it still does the 'wrong' scan first and even stranger > now it doesnt do an indexed scan on subset_table: Well, as Tom said, if you're actually hitting alot of the rows in the subset table, index scan is slower. Plus, I think index scan is only an option on anchored regexps (so what SEARCHTERM is will affect it). If you think that the data is not similar to what it is saying (ie, that you're getting alot of rows), you may not have done a VACUUM ANALYZE recently and the stats could be out of wack, or you might have a very common value that is throwing the optimizer off.