[SQL] problem porting MySQL SQL to Postgres
I've stumbled across a query I don't quite understand the error message for. This query is pulled from a working MySQL setup: SELECT DEWEY_ID, DEWEY_HUNDREDS, DEWEY_TENS, DEWEY_ONES, DEWEY_POINT_ONES, DEWEY_POINT_TENS, DEWEY_POINT_HUNDREDS, DEWEY_POINT_THOUSANDS, DEWEY_TYPE, DEWEY_LANG, DEWEY_SUBJECT FROM lu_dewey WHERE (DEWEY_HUNDREDS = 9) AND (DEWEY_TENS >= 0) AND (DEWEY_TENS <= 9) AND (DEWEY_ONES = 0 || DEWEY_ONES = NULL) AND (DEWEY_POINT_ONES = 0 || DEWEY_POINT_ONES = NULL) AND (DEWEY_POINT_TENS = 0 || DEWEY_POINT_TENS = NULL) AND (DEWEY_POINT_HUNDREDS = 0 || DEWEY_POINT_HUNDREDS = NULL) AND (DEWEY_POINT_THOUSANDS = 0 || DEWEY_POINT_THOUSANDS = NULL) AND (DEWEY_TYPE = 't') AND (DEWEY_LANG = 'en') ORDER BY DEWEY_TENS However I'm getting the following error: ERROR: Unable to identify an operator '=' for types 'character' and 'boolean' You will have to retype this query using an explicit cast. Any help would be much appreciated TIA -- Dan Field ---(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
[SQL] SQL Query Timeouts
I have a problem with a select statement that I am using in a web search engine. I have the following SQL: SELECT da_records.TITLE_EN AS TITLE, da_records.AUTHOR_EN AS AUTHOR, da_records.DESCRIPTION_EN AS DESCRIPTION, da_records.PUBLISHER_EN AS PUBLISHER, da_records.URL_EN AS URL, da_records.RECORD_ID, da_records.KEYWORD_LIST_ID, da_records.LANGUAGE, da_records.CONTRIBUTOR_NAME, da_records.CONTRIBUTOR_EMAIL, da_records.CONTRIBUTOR_ORGANISATION, da_records.CONTRIBUTOR_CREDIT, da_records.DEWEY_LIST_ID, da_records.LISTING_PRIORITY, da_records.SUBMITTED_DATE, da_records.LAST_EDIT_DATE, da_records.STATUS FROM da_records, lu_dewey, da_dewey_list WHERE da_records.RECORD_ID = da_dewey_list.RECORD_ID AND lu_dewey.DEWEY_ID = da_dewey_list.DEWEY_ID AND lu_dewey.DEWEY_LANG = 'en' AND lu_dewey.DEWEY_TYPE = 't' AND da_records.DESCRIPTION_EN like '%nasty%' OR da_records.TITLE_EN like '%nasty%' "nasty" is obviously the search term and if I search for a word which is unlikely to be found, the query returns 0 results in a fairly short time, as would be expected. However, if I search for a common word, the query times out (process gets killed if running it from the pgsql commandline). Its as if the query is returning a resultset which is too big for a buffer or something. I'm afraid I don't know Postgres that well yet so I can't tell you how big the DBs are but in MySQL they were under 10MB total size, so even a complete resultset shouldn't be too much for the machine. Incidentally the hardware I'm running on is: Dual 2GHz Opteron, 2GB RAM, SCSI. Hardly a slow system! Any pointers at either, more efficiend SQL or ways to tweak Postgres will be gladly recieved. Many thanks in Advance -- Dan Field <[EMAIL PROTECTED]> - Support Programmer: Cymru ar y we cy_GB: http://www.cymruarywe.org en_GB: http://www.walesontheweb.org
[SQL] Very slow search using basic pattern matching
I'm trying to use the following statement as part of a search facility on a site I develop. However the search is taking in excess of 10 seconds some times to return results. I'ts a dual opteron server with a couple gigs of RAM so should be more than enough to deal with this database. SELECT da_records.RECORD_ID, da_records.TITLE_EN AS TITLE, da_records.AUTHOR_EN AS AUTHOR, da_records.DESCRIPTION_CY AS DESCRIPTION, da_records.PUBLISHER_CY AS PUBLISHER, da_records.URL_CY AS URL, da_records.RECORD_ID, da_records.KEYWORD_LIST_ID, da_records.LANGUAGE, da_records.CONTRIBUTOR_NAME, da_records.CONTRIBUTOR_EMAIL, da_records.CONTRIBUTOR_ORGANISATION, da_records.CONTRIBUTOR_CREDIT, da_records.DEWEY_LIST_ID, da_records.LISTING_PRIORITY, da_records.SUBMITTED_DATE, da_records.LAST_EDIT_DATE, da_records.STATUS FROM da_records WHERE da_records.DESCRIPTION_CY ~* '.*Aberystwyth*.' OR da_records.TITLE_CY ~* '.*Aberystwyth*.' limit 100 Is there a better way of matching the string? (Must be case insensitive) TIA -- Dan Field <[EMAIL PROTECTED]> - Support Programmer: Cymru ar y we cy_GB: http://www.cymruarywe.org en_GB: http://www.walesontheweb.org
Re: [SQL] Very slow search using basic pattern matching
On 18 May 2004, at 17:12, Bruno Wolff III wrote: On Tue, May 18, 2004 at 16:47:11 +0100, Dan Field <[EMAIL PROTECTED]> wrote: FROM da_records WHERE da_records.DESCRIPTION_CY ~* '.*Aberystwyth*.' OR da_records.TITLE_CY ~* '.*Aberystwyth*.' limit 100 Is there a better way of matching the string? (Must be case insensitive) This is going to require a sequential scan and if you aren't going to hit 100 entries near the beginning of the table, you will be effectively doing a full scan of the table. You might try looking at the tsearch2 contrib entry (a package for full text searching) as that will likely be able to help you out. OK It looks like the full text search is the way forward here, but I can only find the tsearch.sql file in contrib. I'm using RedHat EL 3.0 Advanced Server with PG version 7.3-RH. Is there any documentation available on how to go about creating these indexes on my text fields? I'm at a bit of a loss here, Google is failing me sadly -- Dan Field <[EMAIL PROTECTED]> - Support Programmer: Cymru ar y we cy_GB: http://www.cymruarywe.org en_GB: http://www.walesontheweb.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Memory usage on subselect
On 23 May 2004, at 19:32, Alexander M. Pravking wrote:
BTW, after cancelling the original query postgres freed all the memory,
and used ~7M again, so the leak was not "forever".
I have a similar problem with just one of my queries (although it isn't a sub select):
SELECT
da_records.*
FROM
da_records, lu_dewey, da_dewey_list
WHERE
(da_records.RECORD_ID = da_dewey_list.RECORD_ID) AND
(lu_dewey.DEWEY_ID = da_dewey_list.DEWEY_ID) AND
(lu_dewey.DEWEY_HUNDREDS = 7) AND
(lu_dewey.DEWEY_TENS = 0) AND
(lu_dewey.DEWEY_ONES = 8) AND
(lu_dewey.DEWEY_LANG = 'en') AND
(lu_dewey.DEWEY_TYPE = 't') AND
(lu_dewey.DEWEY_ARCHIVE IS NOT TRUE) AND
(lu_dewey.dewey_point_ones IS NULL) AND
(lu_dewey.dewey_point_tens IS NULL) AND
(lu_dewey.dewey_point_hundreds IS NULL) AND
(lu_dewey.dewey_point_thousands IS NULL) AND
(lu_dewey.dewey_point_tenthousands IS NULL) AND
(lu_dewey.dewey_point_hundredthousands IS NULL)
EXPLAIN results:
Hash Join (cost=57.58..82.89 rows=25 width=661)
Hash Cond: ("outer".record_id = "inner".record_id)
-> Seq Scan on da_records (cost=0.00..20.00 rows=1000 width=649)
-> Hash (cost=57.56..57.56 rows=5 width=12)
-> Hash Join (cost=32.50..57.56 rows=5 width=12)
Hash Cond: ("outer".dewey_id = "inner".dewey_id)
-> Seq Scan on da_dewey_list (cost=0.00..20.00 rows=1000 width=8)
-> Hash (cost=32.50..32.50 rows=1 width=4)
-> Seq Scan on lu_dewey (cost=0.00..32.50 rows=1 width=4)
Filter: (((dewey_hundreds)::text = '7'::text) AND ((dewey_tens)::text = '0'::text) AND ((dewey_ones)::text = '8'::text) AND (dewey_lang = 'en'::bpchar) AND (dewey_type = 't'::bpchar) AND (dewey_archive IS NOT TRUE) AND (dewey_point_ones IS NULL) AND (dewey_point_tens IS NULL) AND (dewey_point_hundreds IS NULL) AND (dewey_point_thousands IS NULL) AND (dewey_point_tenthousands IS NULL) AND (dewey_point_hundredthousands IS NULL))
(10 rows)
--
Dan Field <[EMAIL PROTECTED]> - Support Programmer: Cymru ar y we
cy_GB: http://www.cymruarywe.org
en_GB: http://www.walesontheweb.org
Re: [SQL] Memory usage on subselect
On 24 May 2004, at 14:37, Tom Lane wrote: Dan Field <[EMAIL PROTECTED]> writes: I have a similar problem with just one of my queries (although it isn't a sub select): You really ought to vacuum and/or analyze occasionally. The EXPLAIN results show that the planner hasn't got any non-default statistics for any of these tables. Wow, thanks for that. I'd been pulling my hair out for a couple of days wondering where I was going wrong. I went from 45 second queries down to sub second query lengths after a simple vacuum full analyze. I've now added nightly and monthly cron jobs to do this for me in future. Out of curiosity, why is this deemed a DBA task rather than an automated postgres task? Once again, many thanks. -- Dan Field <[EMAIL PROTECTED]> - Support Programmer: Cymru ar y we cy_GB: http://www.cymruarywe.org en_GB: http://www.walesontheweb.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
