hello all,
on my master-db-server i'm running postgres 7.4.1,
and I have got two slave-servers running postgres 7.4.2
running the following query on the master-server (7.4.1) delivers:
explain analyze select * from foo where bar like '0101%' and foobar like
'top%';
Index Scan using foo_foobar_idx on foo (cost...)
Index Cond: ((foobar>='top: :text) and (firma < 'toq'::text))
Filter: ((bar ~~ '0101%'::text) and (firma ~~ 'top%'::text))
Total runtime: 1.519 ms
the same query on both slaves (7.4.2)(mirrore d from master, same
structure of table etc.) yields:
Seq Scan on foo (cost ....)
Filter: ((bar ~~ '0101%'::text) AND (foobar ~~ 'top%'::text))
Total runtime: 722.331 ms
Is there such a difference in 7.4.1 to 7.4.2 ?
A retardation of 722 ms is not acceptable for me, and I don't see why
the indexes are not used.
Do you have an idea? I have run VACUUM ANALYZE several times, and
REINDEX TABLE foo and even
DROPped and reCREATEd the index. Didn't help.
BTW if I run:
explain analyze select * from foo where bar like '0101%' and
foobar>='top':: text and foobar<'toq'::t ext;
the index is utilized as it is supposed to
Any hint appreciated,
thank you
--
Mit freundlichem Gruß
Henrik Steffen
Geschäftsführer
top concepts Internetmarketi ng GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
-------------------------------------------------------- http://www.topconcepts.de Tel. +49 1805 9977 501*
mail: st*****@topconc epts.de Fax. +49 1805 9977 502*
--------------------------------------------------------
SMS Versand ab 9.9 Cent: http://sms-gw.topconcepts.de
--------------------------------------------------------
Handelsregister : AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------
*) EUR 0,12/Min. (CNS24)
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html 24  2954 
On Wed, Jun 09, 2004 at 09:00:31AM +0200, Henrik Steffen wrote:
hello all,
on my master-db-server i'm running postgres 7.4.1,
and I have got two slave-servers running postgres 7.4.2
<snip>
Is there such a difference in 7.4.1 to 7.4.2 ?
A retardation of 722 ms is not acceptable for me, and I don't see why
the indexes are not used.
Do you have an idea? I have run VACUUM ANALYZE several times, and
REINDEX TABLE foo and even
DROPped and reCREATEd the index. Didn't help.
The classic issue is what encoding are the databases. Anything other
than C and like won't use indexes.
Also, you did a vacuum analyze on both, right?
Hope this helps,
--
Martijn van Oosterhout <kl*****@svana. org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQFAx26jY5T wig3Ge+YRAkiEAJ 94CjEWm7jaNLhUW qMTPZxh5+f8YQCg mkau
Y8Q68BHCViU0tzj 3vVjrW7U=
=9d0w
-----END PGP SIGNATURE-----
On Wed, Jun 09, 2004 at 09:00:31AM +0200, Henrik Steffen wrote:
hello all,
on my master-db-server i'm running postgres 7.4.1,
and I have got two slave-servers running postgres 7.4.2
<snip>
Is there such a difference in 7.4.1 to 7.4.2 ?
A retardation of 722 ms is not acceptable for me, and I don't see why
the indexes are not used.
Do you have an idea? I have run VACUUM ANALYZE several times, and
REINDEX TABLE foo and even
DROPped and reCREATEd the index. Didn't help.
The classic issue is what encoding are the databases. Anything other
than C and like won't use indexes.
Also, you did a vacuum analyze on both, right?
Hope this helps,
--
Martijn van Oosterhout <kl*****@svana. org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQFAx26jY5T wig3Ge+YRAkiEAJ 94CjEWm7jaNLhUW qMTPZxh5+f8YQCg mkau
Y8Q68BHCViU0tzj 3vVjrW7U=
=9d0w
-----END PGP SIGNATURE-----
Martijn van Oosterhout wrote: The classic issue is what encoding are the databases. Anything other
than C and like won't use indexes.
Unless you use text_pattern_op s. See http://www.postgresql.org/docs/7.4/s...s-opclass.html
I think this needs to be in the faq.
---------------------------(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
Martijn van Oosterhout wrote: The classic issue is what encoding are the databases. Anything other
than C and like won't use indexes.
Unless you use text_pattern_op s. See http://www.postgresql.org/docs/7.4/s...s-opclass.html
I think this needs to be in the faq.
---------------------------(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
hello,
I ran vacuum analyze on all 3 servers,
and all servers show the same encoding: SQL_ASCII
any other idea?
--
Mit freundlichem Gruß
Henrik Steffen
Geschäftsführer
top concepts Internetmarketi ng GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
-------------------------------------------------------- http://www.topconcepts.de Tel. +49 1805 9977 501*
mail: st*****@topconc epts.de Fax. +49 1805 9977 502*
--------------------------------------------------------
SMS Versand ab 9.9 Cent: http://sms-gw.topconcepts.de
--------------------------------------------------------
Handelsregister : AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------
*) EUR 0,12/Min. (CNS24) -----Ursprüngliche Nachricht-----
Von: Martijn van Oosterhout [mailto:kl*****@ svana.org]
Gesendet: Mittwoch, 9. Juni 2004 22:10
An: Henrik Steffen
Cc: pgsql
Betreff: Re: [GENERAL] index with LIKE
On Wed, Jun 09, 2004 at 09:00:31AM +0200, Henrik Steffen wrote:
hello all,
on my master-db-server i'm running postgres 7.4.1,
and I have got two slave-servers running postgres 7.4.2
<snip>
Is there such a difference in 7.4.1 to 7.4.2 ?
A retardation of 722 ms is not acceptable for me, and I
don't see why the indexes are not used.
Do you have an idea? I have run VACUUM ANALYZE several times, and
REINDEX TABLE foo and even
DROPped and reCREATEd the index. Didn't help.
The classic issue is what encoding are the databases. Anything other
than C and like won't use indexes.
Also, you did a vacuum analyze on both, right?
Hope this helps,
--
Martijn van Oosterhout <kl*****@svana. org>
http://svana.org/kleptog/ Patent. n. Genius is 5%
inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around
waiting for someone else to do the other 95% so you can sue them.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
hello,
I ran vacuum analyze on all 3 servers,
and all servers show the same encoding: SQL_ASCII
any other idea?
--
Mit freundlichem Gruß
Henrik Steffen
Geschäftsführer
top concepts Internetmarketi ng GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
-------------------------------------------------------- http://www.topconcepts.de Tel. +49 1805 9977 501*
mail: st*****@topconc epts.de Fax. +49 1805 9977 502*
--------------------------------------------------------
SMS Versand ab 9.9 Cent: http://sms-gw.topconcepts.de
--------------------------------------------------------
Handelsregister : AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------
*) EUR 0,12/Min. (CNS24) -----Ursprüngliche Nachricht-----
Von: Martijn van Oosterhout [mailto:kl*****@ svana.org]
Gesendet: Mittwoch, 9. Juni 2004 22:10
An: Henrik Steffen
Cc: pgsql
Betreff: Re: [GENERAL] index with LIKE
On Wed, Jun 09, 2004 at 09:00:31AM +0200, Henrik Steffen wrote:
hello all,
on my master-db-server i'm running postgres 7.4.1,
and I have got two slave-servers running postgres 7.4.2
<snip>
Is there such a difference in 7.4.1 to 7.4.2 ?
A retardation of 722 ms is not acceptable for me, and I
don't see why the indexes are not used.
Do you have an idea? I have run VACUUM ANALYZE several times, and
REINDEX TABLE foo and even
DROPped and reCREATEd the index. Didn't help.
The classic issue is what encoding are the databases. Anything other
than C and like won't use indexes.
Also, you did a vacuum analyze on both, right?
Hope this helps,
--
Martijn van Oosterhout <kl*****@svana. org>
http://svana.org/kleptog/ Patent. n. Genius is 5%
inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around
waiting for someone else to do the other 95% so you can sue them.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Dear group,
I am interested in building an API to a postgres
database using PHP , VB or Python. Python is my best
choice.
Is there any postgres database available to downlaod
and play withit right there. What I mean to say is -
If I can download tables, data and other mapping
information files in .sql files, I can run these files
and make a test db on which I can experiment my API
construction.
Making a Db for now is too much and I do not have
time.
Please help.
Thank you
S.Peri
_______________ _______________ _______________ _____
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Dear group,
I am interested in building an API to a postgres
database using PHP , VB or Python. Python is my best
choice.
Is there any postgres database available to downlaod
and play withit right there. What I mean to say is -
If I can download tables, data and other mapping
information files in .sql files, I can run these files
and make a test db on which I can experiment my API
construction.
Making a Db for now is too much and I do not have
time.
Please help.
Thank you
S.Peri
_______________ _______________ _______________ _____
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
On Sat, 12 Jun 2004, [iso-8859-1] S Peri wrote: Is there any postgres database available to downlaod
and play withit right there. What I mean to say is -
If I can download tables, data and other mapping
information files in .sql files, I can run these files
and make a test db on which I can experiment my API
construction.
AFAICR, EMS PostgreSQL Manager has such a feature (Populating Database).
The 30-day fully-functional version can be downloaded from: http://www.ems-hitech.com/pgmanager/download.phtml
Regards,
- --
Devrim GUNDUZ
devrim~gunduz.o rg devrim.gunduz~l inux.org.tr http://www.tdmsoft.com http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQFAyzjgtl8 6P3SPfQ4RAqdHAJ 40wS2eMLvK0TgEk cMz8QPCsFLG6ACg 3axI
Gv7ob3W8Za1XFhg W3QQK4Fs=
=SXBc
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Robert Brown |
last post by:
If I use _reverse_ wildcard search will it always result in a table
scan? Is it possible to get the DB (Oracle or SQL server) to use
indexes when doing reverse wildcard match?
let's say I have:
table email_address (id int, email varchar)
with the following entries
|
by: Alvar Freude |
last post by:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
the following I posted already on pgsql-bugs -- perhaps someone has a good
workaround or fix or can say me that I'm wrong?
There seems to be a bug in handling bytea columns with index and the
like-operator.
|
by: Dima Tkach |
last post by:
Hi, everybody!
I just ran into a weird problem on 7.3.4.
Here is a simple testcase:
rapidb=# create table nametab (name text);
CREATE TABLE
rapidb=# create index name_idx on nametab(name);
CREATE INDEX
rapidb=# set enable_seqscan=false;
|
by: WalterR |
last post by:
This is my first time here, so there may be earlier relevant threads of
which I am unaware. Though my experience with DB2 is not extensive,
such as it is was under OS/390 or equ. My main experience is IMS DB,
which leads to my question.
In IMS, there is an HDAM access method which can find a record without
using an index as such. At initial database load, it first formats the
entire space allocation into blocks of the given size. ...
|
by: Hervé Piedvache |
last post by:
Hi,
I have may be a stupid question, but I'm a little surprised with some explains
I have, using date fields ...
I would like to understand exactly when index are used ...
I'm using PostgresQL 7.4.1
I have a table with 351 000 records.
I have about 300 to 600 new records by day
| | |
by: Thomas F.O'Connell |
last post by:
I'm interested to know a little bit more about the postgres
implementation of indexes. I'm specifically wondering what it means in
the output of EXPLAIN when a filter is applied.
I'm trying to decide whether it makes sense to use indexes on
expressions rather than relying on a left-anchored LIKE for date
filtering.
Here's what I've got:
|
by: Rich |
last post by:
Yes, I need to store some values in an array type collection object that can
hold 3 or more parameters per index. I have looked at the collection object,
hashtable object and would prefer not to hassel with a multi-dimensional
array. Is there such an object in VB.Net?
Dim obj As someCollectionObj
obj.Add("parmA1", "parmA2", "parmA3")
obj.Add("parmB1", "parmB2", "parmB3")
....
|
by: Russ |
last post by:
Every Python programmer gets this message occasionally:
IndexError: list index out of range
The message tells you where the error occurred, but it doesn't tell you
what the range and the offending index are. Why does it force you to
determine that information for yourself when it could save you a step
and just tell you? This seems like a "no-brainer" to me. Am I missing
something?
|
by: crazy_jutt |
last post by:
hi all,
i heard that db2 ignores indexes when using any column function on the
column which has index on it. but i have seen db2 using indexes even
when using column function.
what is the criteria of db2 about picking index or not picking index
when there is a column function ?
i am using function on one column like below
|
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...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
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...
|
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...
|
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...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
