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)(mirrored 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'::text;
the index is utilized as it is supposed to
Any hint appreciated,
thank you
--
Mit freundlichem Gruß
Henrik Steffen
Geschäftsführer
top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
-------------------------------------------------------- http://www.topconcepts.de Tel. +49 1805 9977 501*
mail: st*****@topconcepts.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  2902 
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
iD8DBQFAx26jY5Twig3Ge+YRAkiEAJ94CjEWm7jaNLhUWqMTPZ xh5+f8YQCgmkau
Y8Q68BHCViU0tzj3vVjrW7U=
=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
iD8DBQFAx26jY5Twig3Ge+YRAkiEAJ94CjEWm7jaNLhUWqMTPZ xh5+f8YQCgmkau
Y8Q68BHCViU0tzj3vVjrW7U=
=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_ops. 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_ops. 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 Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
-------------------------------------------------------- http://www.topconcepts.de Tel. +49 1805 9977 501*
mail: st*****@topconcepts.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 Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
-------------------------------------------------------- http://www.topconcepts.de Tel. +49 1805 9977 501*
mail: st*****@topconcepts.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.org devrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQFAyzjgtl86P3SPfQ4RAqdHAJ40wS2eMLvK0TgEkcMz8Q PCsFLG6ACg3axI
Gv7ob3W8Za1XFhgW3QQK4Fs=
=SXBc
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
-----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.org devrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQFAyzjgtl86P3SPfQ4RAqdHAJ40wS2eMLvK0TgEkcMz8Q PCsFLG6ACg3axI
Gv7ob3W8Za1XFhgW3QQK4Fs=
=SXBc
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
"Henrik Steffen" <st*****@city-map.de> writes: any other idea?
Let's see EXPLAIN ANALYZE results from both servers?
Also, can you force an indexscan to be chosen by setting enable_seqscan
off? If so, let's see that EXPLAIN ANALYZE too.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
"Henrik Steffen" <st*****@city-map.de> writes: any other idea?
Let's see EXPLAIN ANALYZE results from both servers?
Also, can you force an indexscan to be chosen by setting enable_seqscan
off? If so, let's see that EXPLAIN ANALYZE too.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
hi tom,
here the results of
EXPLAIN ANALYZE SELECT * FROM basiseintrag WHERE kundennummer LIKE
'0101%' AND firma LIKE 'top con%';
run on all three servers:
main server:
QUERY PLAN
------------------------------------------------------------------------
---------------------------------------------------------------
Index Scan using basiseintrag_firma_idx on basiseintrag
(cost=0.00..6.01 rows=1 width=265) (actual time=0.315..0.393 rows=2
loops=1)
Index Cond: ((firma >= 'top con'::text) AND (firma < 'top
coo'::text))
Filter: ((kundennummer ~~ '0101%'::text) AND (firma ~~ 'top
con%'::text))
Total runtime: 0.784 ms
(4 rows)
slave 1:
QUERY PLAN
------------------------------------------------------------------------
----------------------------------------
Seq Scan on basiseintrag (cost=0.00..22214.97 rows=1 width=263)
(actual time=484.111..605.169 rows=1 loops=1)
Filter: ((kundennummer ~~ '0101%'::text) AND (firma ~~ 'top
con%'::text))
Total runtime: 605.424 ms
(3 rows)
slave 2:
QUERY PLAN
------------------------------------------------------------------------
----------------------------------------
Seq Scan on basiseintrag (cost=0.00..24064.96 rows=1 width=264)
(actual time=569.324..589.362 rows=1 loops=1)
Filter: ((kundennummer ~~ '0101%'::text) AND (firma ~~ 'top
con%'::text))
Total runtime: 589.589 ms
(3 rows)
and now this is with enable_seqscan set to off on slave 1:
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------
Seq Scan on basiseintrag (cost=100000000.00..100024064.96 rows=1
width=264) (actual time=1028.468..1092.537 rows=1 loops=1)
Filter: ((kundennummer ~~ '0101%'::text) AND (firma ~~ 'top
con%'::text))
Total runtime: 1093.320 ms
(3 rows)
--
Mit freundlichem Gruß
Henrik Steffen
Geschäftsführer
top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
-------------------------------------------------------- http://www.topconcepts.de Tel. +49 1805 9977 501*
mail: st*****@topconcepts.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 7: don't forget to increase your free space map settings
hi tom,
here the results of
EXPLAIN ANALYZE SELECT * FROM basiseintrag WHERE kundennummer LIKE
'0101%' AND firma LIKE 'top con%';
run on all three servers:
main server:
QUERY PLAN
------------------------------------------------------------------------
---------------------------------------------------------------
Index Scan using basiseintrag_firma_idx on basiseintrag
(cost=0.00..6.01 rows=1 width=265) (actual time=0.315..0.393 rows=2
loops=1)
Index Cond: ((firma >= 'top con'::text) AND (firma < 'top
coo'::text))
Filter: ((kundennummer ~~ '0101%'::text) AND (firma ~~ 'top
con%'::text))
Total runtime: 0.784 ms
(4 rows)
slave 1:
QUERY PLAN
------------------------------------------------------------------------
----------------------------------------
Seq Scan on basiseintrag (cost=0.00..22214.97 rows=1 width=263)
(actual time=484.111..605.169 rows=1 loops=1)
Filter: ((kundennummer ~~ '0101%'::text) AND (firma ~~ 'top
con%'::text))
Total runtime: 605.424 ms
(3 rows)
slave 2:
QUERY PLAN
------------------------------------------------------------------------
----------------------------------------
Seq Scan on basiseintrag (cost=0.00..24064.96 rows=1 width=264)
(actual time=569.324..589.362 rows=1 loops=1)
Filter: ((kundennummer ~~ '0101%'::text) AND (firma ~~ 'top
con%'::text))
Total runtime: 589.589 ms
(3 rows)
and now this is with enable_seqscan set to off on slave 1:
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------
Seq Scan on basiseintrag (cost=100000000.00..100024064.96 rows=1
width=264) (actual time=1028.468..1092.537 rows=1 loops=1)
Filter: ((kundennummer ~~ '0101%'::text) AND (firma ~~ 'top
con%'::text))
Total runtime: 1093.320 ms
(3 rows)
--
Mit freundlichem Gruß
Henrik Steffen
Geschäftsführer
top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
-------------------------------------------------------- http://www.topconcepts.de Tel. +49 1805 9977 501*
mail: st*****@topconcepts.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 7: don't forget to increase your free space map settings
1: Is index capable of being used on the slaves, or are they just making
bad decisions? Try disabling seq scans on the slave servers for testing
and see if they can use the index. Use "set enable_seqscan = FALSE;"
If they can't use the index then, then they are likely in the wrong
locale, and you'll need to re initdb them to get them to use an index.
If they can use the index then we have tuning issue.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
1: Is index capable of being used on the slaves, or are they just making
bad decisions? Try disabling seq scans on the slave servers for testing
and see if they can use the index. Use "set enable_seqscan = FALSE;"
If they can't use the index then, then they are likely in the wrong
locale, and you'll need to re initdb them to get them to use an index.
If they can use the index then we have tuning issue.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
hello scott,
disable enable_seqscan still does no force the backend
to use indexes.
so it looks like a locale problem, right?
I checked lc_* vars on both servers:
type mainserver slave
lc_collate C de_DE.UTF-8
lc_ctype de_DE@euro de_DE.UTF-8
lc_messages de_DE@euro de_DE.UTF-8
.....
I guest "lc_collate" is the problem, isn't it? pg*****************@postgresql.org wrote: 1: Is index capable of being used on the slaves, or are they
just making
bad decisions? Try disabling seq scans on the slave servers
for testing
and see if they can use the index. Use "set enable_seqscan = FALSE;"
If they can't use the index then, then they are likely in the wrong
locale, and you'll need to re initdb them to get them to use an index.
If they can use the index then we have tuning issue.
---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to
ma*******@postgresql.org)
--
Mit freundlichem Gruß
Henrik Steffen
Geschäftsführer
top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
-------------------------------------------------------- http://www.topconcepts.de Tel. +49 1805 9977 501*
mail: st*****@topconcepts.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 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
hello scott,
disable enable_seqscan still does no force the backend
to use indexes.
so it looks like a locale problem, right?
I checked lc_* vars on both servers:
type mainserver slave
lc_collate C de_DE.UTF-8
lc_ctype de_DE@euro de_DE.UTF-8
lc_messages de_DE@euro de_DE.UTF-8
.....
I guest "lc_collate" is the problem, isn't it? pg*****************@postgresql.org wrote: 1: Is index capable of being used on the slaves, or are they
just making
bad decisions? Try disabling seq scans on the slave servers
for testing
and see if they can use the index. Use "set enable_seqscan = FALSE;"
If they can't use the index then, then they are likely in the wrong
locale, and you'll need to re initdb them to get them to use an index.
If they can use the index then we have tuning issue.
---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to
ma*******@postgresql.org)
--
Mit freundlichem Gruß
Henrik Steffen
Geschäftsführer
top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
-------------------------------------------------------- http://www.topconcepts.de Tel. +49 1805 9977 501*
mail: st*****@topconcepts.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 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
Henrik Steffen wrote: hello scott,
disable enable_seqscan still does no force the backend
to use indexes.
so it looks like a locale problem, right?
I checked lc_* vars on both servers:
type mainserver slave
lc_collate C de_DE.UTF-8
lc_ctype de_DE@euro de_DE.UTF-8
lc_messages de_DE@euro de_DE.UTF-8
....
I guest "lc_collate" is the problem, isn't it?
If it's an encoding issue, then you may need to change the index
operator type as suggested in one of the previous replies:
Check out the link to the indexes-opclass below, and try recreating one
of the indexes in the slave with a different index operator, and see if
the index starts getting used. Of course it's a pain because the schemas
are then slightly different... but then so is the encoding...
Hope that helps. If it does please let us know. Thanks.
John Sidney-Woollett
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_ops. See http://www.postgresql.org/docs/7.4/s...s-opclass.html
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Henrik Steffen wrote: hello scott,
disable enable_seqscan still does no force the backend
to use indexes.
so it looks like a locale problem, right?
I checked lc_* vars on both servers:
type mainserver slave
lc_collate C de_DE.UTF-8
lc_ctype de_DE@euro de_DE.UTF-8
lc_messages de_DE@euro de_DE.UTF-8
....
I guest "lc_collate" is the problem, isn't it?
If it's an encoding issue, then you may need to change the index
operator type as suggested in one of the previous replies:
Check out the link to the indexes-opclass below, and try recreating one
of the indexes in the slave with a different index operator, and see if
the index starts getting used. Of course it's a pain because the schemas
are then slightly different... but then so is the encoding...
Hope that helps. If it does please let us know. Thanks.
John Sidney-Woollett
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_ops. See http://www.postgresql.org/docs/7.4/s...s-opclass.html
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
hello john,
thanks for your email!
changing the index type to "text_pattern_ops" solved the problem.
I didn't quite get the point, when Joseph Shraibman first sent the link
regarding operator classes. My apologies.
However, I would not fancy to change all (hundrets) of indexes now.
Would changing the lc_collate setting to 'C' solve this issue as well?
Thanks again,
--
Mit freundlichem Gruß
Henrik Steffen
Geschäftsführer
top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
-------------------------------------------------------- http://www.topconcepts.de Tel. +49 1805 9977 501*
mail: st*****@topconcepts.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: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org] Im Auftrag von
John Sidney-Woollett
Gesendet: Donnerstag, 17. Juni 2004 11:36
An: Henrik Steffen
Cc: 'Scott Marlowe'; pgsql
Betreff: Re: [GENERAL] index with LIKE
Henrik Steffen wrote:
hello scott,
disable enable_seqscan still does no force the backend
to use indexes.
so it looks like a locale problem, right?
I checked lc_* vars on both servers:
type mainserver slave
lc_collate C de_DE.UTF-8
lc_ctype de_DE@euro de_DE.UTF-8
lc_messages de_DE@euro de_DE.UTF-8
....
I guest "lc_collate" is the problem, isn't it?
If it's an encoding issue, then you may need to change the index
operator type as suggested in one of the previous replies:
Check out the link to the indexes-opclass below, and try
recreating one
of the indexes in the slave with a different index operator,
and see if
the index starts getting used. Of course it's a pain because
the schemas
are then slightly different... but then so is the encoding...
Hope that helps. If it does please let us know. Thanks.
John Sidney-Woollett
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_ops. See
http://www.postgresql.org/docs/7.4/s...s-opclass.html
---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
hello john,
thanks for your email!
changing the index type to "text_pattern_ops" solved the problem.
I didn't quite get the point, when Joseph Shraibman first sent the link
regarding operator classes. My apologies.
However, I would not fancy to change all (hundrets) of indexes now.
Would changing the lc_collate setting to 'C' solve this issue as well?
Thanks again,
--
Mit freundlichem Gruß
Henrik Steffen
Geschäftsführer
top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
-------------------------------------------------------- http://www.topconcepts.de Tel. +49 1805 9977 501*
mail: st*****@topconcepts.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: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org] Im Auftrag von
John Sidney-Woollett
Gesendet: Donnerstag, 17. Juni 2004 11:36
An: Henrik Steffen
Cc: 'Scott Marlowe'; pgsql
Betreff: Re: [GENERAL] index with LIKE
Henrik Steffen wrote:
hello scott,
disable enable_seqscan still does no force the backend
to use indexes.
so it looks like a locale problem, right?
I checked lc_* vars on both servers:
type mainserver slave
lc_collate C de_DE.UTF-8
lc_ctype de_DE@euro de_DE.UTF-8
lc_messages de_DE@euro de_DE.UTF-8
....
I guest "lc_collate" is the problem, isn't it?
If it's an encoding issue, then you may need to change the index
operator type as suggested in one of the previous replies:
Check out the link to the indexes-opclass below, and try
recreating one
of the indexes in the slave with a different index operator,
and see if
the index starts getting used. Of course it's a pain because
the schemas
are then slightly different... but then so is the encoding...
Hope that helps. If it does please let us know. Thanks.
John Sidney-Woollett
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_ops. See
http://www.postgresql.org/docs/7.4/s...s-opclass.html
---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
Henrik
Thanks for the info.
What encoding you should use depends on your data, and how you want
records sorted etc. You'll have to figure out what is more suitable for
you - I cannot answer that for you.
To solve your master/slave index problem, why not rebuild the slave
databases using the C encoding instead of your de_DE.UTF-8 encoding?
Otherwise create a SQL statement to extract all the (text) indexes from
your database where a like operation will be used, and use it to drop
and re-create the index.
Something along these lines can be used to drop your indexes ( you need
to execute the results from the query)
SELECT 'drop index '||n.nspname||'.'||c.relname||';'
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('i','')
AND n.nspname IN ('customer', 'photo')
AND c.relname NOT LIKE '%_pkey';
But you'll need to be more specific about which schemas and indexes to
drop and re-create. The SQL to regenerate the new indexes, I'll leave to
you to figure out! :)
Good luck.
John Sidney-Woollett
Henrik Steffen wrote: hello john,
thanks for your email!
changing the index type to "text_pattern_ops" solved the problem.
I didn't quite get the point, when Joseph Shraibman first sent the link
regarding operator classes. My apologies.
However, I would not fancy to change all (hundrets) of indexes now.
Would changing the lc_collate setting to 'C' solve this issue as well?
Thanks again,
--
Mit freundlichem Gruß
Henrik Steffen
Geschäftsführer
top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.de Tel. +49 1805 9977 501*
mail: st*****@topconcepts.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: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org] Im Auftrag von
John Sidney-Woollett
Gesendet: Donnerstag, 17. Juni 2004 11:36
An: Henrik Steffen
Cc: 'Scott Marlowe'; pgsql
Betreff: Re: [GENERAL] index with LIKE
Henrik Steffen wrote:
hello scott,
disable enable_seqscan still does no force the backend
to use indexes.
so it looks like a locale problem, right?
I checked lc_* vars on both servers:
type mainserver slave
lc_collate C de_DE.UTF-8
lc_ctype de_DE@euro de_DE.UTF-8
lc_messages de_DE@euro de_DE.UTF-8
....
I guest "lc_collate" is the problem, isn't it?
If it's an encoding issue, then you may need to change the index
operator type as suggested in one of the previous replies:
Check out the link to the indexes-opclass below, and try
recreating one
of the indexes in the slave with a different index operator,
and see if
the index starts getting used. Of course it's a pain because
the schemas
are then slightly different... but then so is the encoding...
Hope that helps. If it does please let us know. Thanks.
John Sidney-Woollett
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_ops. See
http://www.postgresql.org/docs/7.4/s...s-opclass.html
---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
Henrik
Thanks for the info.
What encoding you should use depends on your data, and how you want
records sorted etc. You'll have to figure out what is more suitable for
you - I cannot answer that for you.
To solve your master/slave index problem, why not rebuild the slave
databases using the C encoding instead of your de_DE.UTF-8 encoding?
Otherwise create a SQL statement to extract all the (text) indexes from
your database where a like operation will be used, and use it to drop
and re-create the index.
Something along these lines can be used to drop your indexes ( you need
to execute the results from the query)
SELECT 'drop index '||n.nspname||'.'||c.relname||';'
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('i','')
AND n.nspname IN ('customer', 'photo')
AND c.relname NOT LIKE '%_pkey';
But you'll need to be more specific about which schemas and indexes to
drop and re-create. The SQL to regenerate the new indexes, I'll leave to
you to figure out! :)
Good luck.
John Sidney-Woollett
Henrik Steffen wrote: hello john,
thanks for your email!
changing the index type to "text_pattern_ops" solved the problem.
I didn't quite get the point, when Joseph Shraibman first sent the link
regarding operator classes. My apologies.
However, I would not fancy to change all (hundrets) of indexes now.
Would changing the lc_collate setting to 'C' solve this issue as well?
Thanks again,
--
Mit freundlichem Gruß
Henrik Steffen
Geschäftsführer
top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.de Tel. +49 1805 9977 501*
mail: st*****@topconcepts.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: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org] Im Auftrag von
John Sidney-Woollett
Gesendet: Donnerstag, 17. Juni 2004 11:36
An: Henrik Steffen
Cc: 'Scott Marlowe'; pgsql
Betreff: Re: [GENERAL] index with LIKE
Henrik Steffen wrote:
hello scott,
disable enable_seqscan still does no force the backend
to use indexes.
so it looks like a locale problem, right?
I checked lc_* vars on both servers:
type mainserver slave
lc_collate C de_DE.UTF-8
lc_ctype de_DE@euro de_DE.UTF-8
lc_messages de_DE@euro de_DE.UTF-8
....
I guest "lc_collate" is the problem, isn't it?
If it's an encoding issue, then you may need to change the index
operator type as suggested in one of the previous replies:
Check out the link to the indexes-opclass below, and try
recreating one
of the indexes in the slave with a different index operator,
and see if
the index starts getting used. Of course it's a pain because
the schemas
are then slightly different... but then so is the encoding...
Hope that helps. If it does please let us know. Thanks.
John Sidney-Woollett
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_ops. See
http://www.postgresql.org/docs/7.4/s...s-opclass.html
---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly 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:...
|
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...
|
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);...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
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...
|
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...
|
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,...
| |
