473,838 Members | 1,624 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

website doc search is extremely SLOW

Trying to use the 'search' in the docs section of PostgreSQL.org
is extremely SLOW. Considering this is a website for a database
and databases are supposed to be good for indexing content, I'd
expect a much faster performance.

I submitted my search over two minutes ago. I just finished this
email to the list. The results have still not come back. I only
searched for:

SECURITY INVOKER

Perhaps this should be worked on?

Dante

---------------------------(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

Nov 12 '05
83 5977
While you are in there - consider looking at effective_cache _size too.
Set it to something like your average buffer cache memory.

As I understand this, it only effects the choices of possible plans - so
with the default (1000) some good ones that use more memory may be
ignored (mind you - some really bad ones may be ignored too).

best wishes

Mark

Tom Lane wrote:

is this something that can be set database wide,


Yeah, see default_statist ics_target in postgresql.conf .

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 12 '05 #51
"Marc G. Fournier" <sc*****@postgr esql.org> writes:
On Thu, 1 Jan 2004, Tom Lane wrote:
"Marc G. Fournier" <sc*****@postgr esql.org> writes:
what sort of impact does CLUSTER have on the system? For instance, an
index happens nightly, so I'm guessing that I'll have to CLUSTER each
right after?
Depends; what does the "index" process do --- are ndict8 and friends
rebuilt from scratch?

nope, but heavily updated ... basically, the indexer looks at url for what
urls need to be 're-indexed' ... if it does, it removed all words from the
ndict# tables that belong to that url, and re-adds accordingly ...


Hmm, but in practice only a small fraction of the pages on the site
change in any given day, no? I'd think the typical nightly run changes
only a small fraction of the entries in the tables, if it is smart
enough not to re-index pages that did not change.

My guess is that it'd be enough to re-cluster once a week or so.

But this is pointless speculation until we find out whether clustering
helps enough to make it worth maintaining clustered-ness at all. Did
you get any results yet?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #52
On Thu, 1 Jan 2004, Tom Lane wrote:
"Marc G. Fournier" <sc*****@postgr esql.org> writes:
On Thu, 1 Jan 2004, Tom Lane wrote:
"Marc G. Fournier" <sc*****@postgr esql.org> writes:
what sort of impact does CLUSTER have on the system? For instance, an
index happens nightly, so I'm guessing that I'll have to CLUSTER each
right after?

Depends; what does the "index" process do --- are ndict8 and friends
rebuilt from scratch?
nope, but heavily updated ... basically, the indexer looks at url for what
urls need to be 're-indexed' ... if it does, it removed all words from the
ndict# tables that belong to that url, and re-adds accordingly ...


Hmm, but in practice only a small fraction of the pages on the site
change in any given day, no? I'd think the typical nightly run changes
only a small fraction of the entries in the tables, if it is smart
enough not to re-index pages that did not change.


that is correct, and I further restrict it to 10000 URLs a night ...
My guess is that it'd be enough to re-cluster once a week or so.

But this is pointless speculation until we find out whether clustering
helps enough to make it worth maintaining clustered-ness at all. Did
you get any results yet?


Its doing the CLUSTERing right now ... will post results once finished ...

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: sc*****@hub.org Yahoo!: yscrappy ICQ: 7615664

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #53
Yup,

So slow in fact that I never use it. I did once or twice and gave up.
It is ironic! I only come to the online docs when I already know the
<where> part of my search and just go to that part or section. For
everything else, there's google!

SECURITY INVOKER site:postgresql .org

Searched pages from postgresql.org for SECURITY INVOKER. Results 1 -
10 of about 141. Search took 0.23 seconds.
Ahhh, that's better.

Or use site:www.postgresql.org to avoid the archive listings, etc.

== Ezra Epstein

""D. Dante Lorenso"" <da***@lorenso. com> wrote in message
news:3F******** ******@lorenso. com...
Trying to use the 'search' in the docs section of PostgreSQL.org
is extremely SLOW. Considering this is a website for a database
and databases are supposed to be good for indexing content, I'd
expect a much faster performance.

I submitted my search over two minutes ago. I just finished this
email to the list. The results have still not come back. I only
searched for:

SECURITY INVOKER

Perhaps this should be worked on?

Dante

---------------------------(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

Nov 12 '05 #54
On Thu, 1 Jan 2004, Tom Lane wrote:
"Marc G. Fournier" <sc*****@postgr esql.org> writes:
On Thu, 1 Jan 2004, Tom Lane wrote:
"Marc G. Fournier" <sc*****@postgr esql.org> writes:
what sort of impact does CLUSTER have on the system? For instance, an
index happens nightly, so I'm guessing that I'll have to CLUSTER each
right after?

Depends; what does the "index" process do --- are ndict8 and friends
rebuilt from scratch?

nope, but heavily updated ... basically, the indexer looks at url for what
urls need to be 're-indexed' ... if it does, it removed all words from the
ndict# tables that belong to that url, and re-adds accordingly ...


Hmm, but in practice only a small fraction of the pages on the site
change in any given day, no? I'd think the typical nightly run changes
only a small fraction of the entries in the tables, if it is smart
enough not to re-index pages that did not change.

My guess is that it'd be enough to re-cluster once a week or so.

But this is pointless speculation until we find out whether clustering
helps enough to make it worth maintaining clustered-ness at all. Did
you get any results yet?


Here is post-CLUSTER:

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..194 70.40 rows=1952 width=8) (actual time=39.639..42 00.376 rows=13415 loops=1)
-> Index Scan using n8_word on ndict8 (cost=0.00..70. 90 rows=3253 width=8) (actual time=37.047..28 02.400 rows=15533 loops=1)
Index Cond: (word_id = 417851441)
-> Index Scan using url_rec_id on url (cost=0.00..5.9 5 rows=1 width=4) (actual time=0.061..0.0 68 rows=1 loops=15533)
Index Cond: (url.rec_id = "outer".url _id)
Filter: (url ~~ 'http://archives.postgr esql.org/%%'::text)
Total runtime: 4273.799 ms
(7 rows)

And ... shit ... just tried a search on 'security invoker', and results
back in 2 secs ... 'multi version', 18 secs ... 'mnogosearch', .32sec ...
'mnogosearch performance', 18secs ...

this is closer to what I expect from PostgreSQL ...

I'm still loading the 'WITHOUT OIDS' database ... should I expect that,
with CLUSTERing, its performance would be slightly better yet, or would
the difference be negligible?

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: sc*****@hub.org Yahoo!: yscrappy ICQ: 7615664

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #55
"Marc G. Fournier" <sc*****@postgr esql.org> writes:
I'm still loading the 'WITHOUT OIDS' database ... should I expect that,
with CLUSTERing, its performance would be slightly better yet, or would
the difference be negligible?


I think the difference will be marginal, but worth doing; you're
reducing the row size from 40 bytes to 36 if I counted correctly,
so circa-10% I/O saving, no?

24 bytes minimum 7.4 HeapTupleHeader
4 bytes OID
12 bytes three int4 fields

On a machine with 8-byte MAXALIGN, this would not help, but on
Intel hardware it should.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #56
On Thu, 1 Jan 2004, Tom Lane wrote:
"Marc G. Fournier" <sc*****@postgr esql.org> writes:
I'm still loading the 'WITHOUT OIDS' database ... should I expect that,
with CLUSTERing, its performance would be slightly better yet, or would
the difference be negligible?


I think the difference will be marginal, but worth doing; you're
reducing the row size from 40 bytes to 36 if I counted correctly,
so circa-10% I/O saving, no?

24 bytes minimum 7.4 HeapTupleHeader
4 bytes OID
12 bytes three int4 fields

On a machine with 8-byte MAXALIGN, this would not help, but on
Intel hardware it should.


I take it there is no way of drop'ng OIDs after the fact, eh? :)

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: sc*****@hub.org Yahoo!: yscrappy ICQ: 7615664

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #57
"Marc G. Fournier" <sc*****@postgr esql.org> writes:
I take it there is no way of drop'ng OIDs after the fact, eh? :)


I think we have an ALTER TABLE DROP OIDS command, but it won't instantly
remove the OIDS from the table --- removal happens incrementally as rows
get updated. Maybe that's good enough for your situation though.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #58
On Thu, 1 Jan 2004, Tom Lane wrote:
"Marc G. Fournier" <sc*****@postgr esql.org> writes:
I take it there is no way of drop'ng OIDs after the fact, eh? :)


I think we have an ALTER TABLE DROP OIDS command, but it won't instantly
remove the OIDS from the table --- removal happens incrementally as rows
get updated. Maybe that's good enough for your situation though.


actually, that would be perfect ... saves having to spend the many many
hours to re-index all the URLs, and will at least give a gradual
improvement :)

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: sc*****@hub.org Yahoo!: yscrappy ICQ: 7615664

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #59

Note that I haven't made many changes to the postgresql.conf file, so
there might be something really obvious I've overlooked, but here are the
uncommented ones (ie. ones I've modified from defaults):

tcpip_socket = true
max_connection s = 512
shared_buffe rs = 10000 # min 16, at least max_connections *2, 8KB each
sort_mem = 10240 # min 64, size in KB
vacuum_mem = 81920 # min 1024, size in KB


what about effective_cache _size and random_page_cos t?
Sincerely,

Joshua D. Drake

syslog = 2 # range 0-2; 0=stdout; 1=both; 2=syslog
syslog_facilit y = 'LOCAL0'
syslog_ident = 'postgres'
log_connection s = true
log_duration = false
log_statemen t = false
lc_messages = 'C' # locale for system error message strings
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: sc*****@hub.org Yahoo!: yscrappy ICQ: 7615664

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - jd@commandpromp t.com - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #60

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
2238
by: bettina | last post by:
I'm re-programming my Website (www.coaster.ch) in PHP and I find it too slow (although I have ADSL). That's more or less how it functions: Here my tables: 'COASTERS' (code of coaster, code of country, etc...) 'COUNTRIES' (code of country, names of countries in different languages, code of continent) 'CONTINENTS' (code of continent, names of continents in different languages)
12
6481
by: Vjay77 | last post by:
Hi, I haven't posted any problem in quite a while now, but I came to the point that I really need to ask for help. I need to create an application which will search through .txt log file and find all lines where email from hotmail occured. All these emails need to be printed to list box on the form. Problem with code you'll see below, is that it takes long time to
4
4778
by: sommes | last post by:
It's only happen on .asp website, what's the problem? Thank you
2
2079
by: tmb | last post by:
When publishing a website the process is excrutiatingly slow - we are talking 3-4 minutes from when the actual transfer to the site has begun to completion. Apparently i'm not the only one experiencing this and searching on the net i found a possible solution: http://blog.n-technologies.be/CommentView.aspx?guid=3df1930b-9517-4b9b-9dd6-b59cbcbbe34d However, i don't quite understand how to actually apply the solution mentioned. I have...
2
5040
by: yasmike | last post by:
I am having a problem with my secure website on our internal network. The secure website is hosted on our Windows 2000 Server running IIS 5.0. If you try and access the website from a browser from another computer on the same internal network using its domain name, https://www.domainname .com, it is extremely slow. If you access it using its IP https://192.168.1.2 it is very quick. It is also quick for anyone outside the internal network to...
0
9853
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10903
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10584
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10289
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9423
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7827
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7014
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5681
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5864
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.