By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,617 Members | 1,596 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,617 IT Pros & Developers. It's quick & easy.

index with LIKE

P: n/a

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

Nov 23 '05 #1
Share this Question
Share on Google+
24 Replies


P: n/a
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-----

Nov 23 '05 #2

P: n/a
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-----

Nov 23 '05 #3

P: n/a


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

Nov 23 '05 #4

P: n/a


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

Nov 23 '05 #5

P: n/a

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

Nov 23 '05 #6

P: n/a

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

Nov 23 '05 #7

P: n/a
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

Nov 23 '05 #8

P: n/a
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

Nov 23 '05 #9

P: n/a
-----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

Nov 23 '05 #10

P: n/a
-----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

Nov 23 '05 #11

P: n/a
"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

Nov 23 '05 #12

P: n/a
"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

Nov 23 '05 #13

P: n/a

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

Nov 23 '05 #14

P: n/a

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

Nov 23 '05 #15

P: n/a
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)

Nov 23 '05 #16

P: n/a
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)

Nov 23 '05 #17

P: n/a

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

Nov 23 '05 #18

P: n/a

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

Nov 23 '05 #19

P: n/a
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

Nov 23 '05 #20

P: n/a
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

Nov 23 '05 #21

P: n/a

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

Nov 23 '05 #22

P: n/a

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

Nov 23 '05 #23

P: n/a
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

Nov 23 '05 #24

P: n/a
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

Nov 23 '05 #25

This discussion thread is closed

Replies have been disabled for this discussion.