Thread: index with LIKE
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: steffen@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)
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 <kleptog@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.
Attachment
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/static/indexes-opclass.html I think this needs to be in the faq.
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: steffen@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:kleptog@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 <kleptog@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. >
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
-----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) iD8DBQFAyzjgtl86P3SPfQ4RAqdHAJ40wS2eMLvK0TgEkcMz8QPCsFLG6ACg3axI Gv7ob3W8Za1XFhgW3QQK4Fs= =SXBc -----END PGP SIGNATURE-----
"Henrik Steffen" <steffen@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
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: steffen@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)
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.
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? pgsql-general-owner@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 > majordomo@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: steffen@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)
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/static/indexes-opclass.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: steffen@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: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@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/static/indexes-opclass.html > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > http://www.postgresql.org/docs/faqs/FAQ.html
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: steffen@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: pgsql-general-owner@postgresql.org
>>[mailto:pgsql-general-owner@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/static/indexes-opclass.html
>>
>>
>>---------------------------(end of
>>broadcast)---------------------------
>>TIP 5: Have you checked our extensive FAQ?
>>
>>
>>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>
>