[SQL] improvements to query with hierarchical elements
Greetings, I have a complex query which I am trying to figure out the most efficient way of performing. My database is laid out as follows: items -have_many-> events -have_many-> event_locations -have_many-> locations also rows in the location_links table link two locations together in a parent-child relationship and rows in the location_descendants table provide a full list of the descendants of a particular location. I am trying to find all locations which both are direct children of a given parent location, and are associated with at least one item in a constrained subset of items. (eg. Find all states of the USA in which at least one wooden axe was made. Also find the number of wooden axes made in each state.) I have developed the following query: SELECT locations.*, location_ids.item_count AS item_count FROMlocations JOIN (SELECT immediate_descendants.ancestor_id AS id, COUNT(DISTINCT creation_events.item_id) AS item_count FROMevent_locations JOIN (SELECT * FROMlocation_descendants WHERE ancestor_id IN (SELECT child_id FROMlocation_links WHERE parent_id = *note 1* ) ) AS immediate_descendants ON event_locations.location_id = immediate_descendants.descendant_id JOIN (SELECT * FROMevents WHERE item_id IN (*note 2*) AND association = 'creation' ) AS creation_events ON event_locations.event_id = creation_events.id GROUP BY immediate_descendants.ancestor_id ) AS location_ids ON locations.id = location_ids.id *note 1* - the id of the parent location. *note 2* - the query which returns a list of constrained item ids This works but I am looking for any way to improve the performance of the query (including changing the layout of the tables). Any ideas, suggestions or general pointers would be greatly appreciated. Thanks very much, Ryan
[SQL] Working with dates before 4713 BC
Hi all, I am building an application which requires the storage of dates relating to the creation of archaeological items. The problem I am facing is that although most of the dates are working fine, some of the items were created before the beginning of recorded history (4713 BC). Does anyone have any suggestions for making these dates play nicely with items created more recently (January 10th, 1968 for example)? My only other requirement is that I need to be able to execute SQL queries which can find items made before a certain date, after a certain date, or between two dates. Cheers, Ryan
[SQL] Identifying which column matches a full text search
Hi all,
The following example is given in the Postgres 8.3 manual regarding how to
create a single ts_vector column for two existing columns:
ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector;
UPDATE pgweb SET textsearchable_index_col =
to_tsvector('english', coalesce(title,'') || coalesce(body,''));
Then we create a GIN index to speed up the search:
CREATE INDEX textsearch_idx ON pgweb USING gin(textsearchable_index_col);
Now we are ready to perform a fast full text search:
SELECT title
FROM pgweb
WHERE textsearchable_index_col @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC LIMIT 10;
Using this approach. Is there any way of retrieving which of the original
two columns the match was found in?
Any help would be much appreciated,
Ryan
Re: [SQL] Identifying which column matches a full text search
Richard Huxton wrote:
>
> Failing that, where I've had many (a dozen) different sources but want
> to search them all I've built a textsearch_blocks table with columns to
> identify the source and have triggers that keep it up to date.
Once you've built the text search blocks table, how do you search it? Do you
perform
twelve separate queries or can you just do one?
Ryan
Ryan Wallace wrote:
>
> UPDATE pgweb SET textsearchable_index_col =
> to_tsvector('english', coalesce(title,'') || coalesce(body,''));
> WHERE textsearchable_index_col @@ to_tsquery('create & table')
> Using this approach. Is there any way of retrieving which of the original
> two columns the match was found in?
Afraid not - you're not indexing two columns, you're indexing one:
textsearchable_index_col.
You can add up to four weights to a tsvector though, typically for
title/body matching. See chapter 12.3 for details.
Failing that, where I've had many (a dozen) different sources but want
to search them all I've built a textsearch_blocks table with columns to
identify the source and have triggers that keep it up to date.
--
Richard Huxton
Archonet Ltd
No virus found in this incoming message.
Checked by AVG - http://www.avg.com
Version: 8.0.138 / Virus Database: 270.5.6/1579 - Release Date: 7/29/2008
6:43 AM
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Single Quote in tsquery
Hi all, I am trying to perform a full text search for the word 'ksan (which starts with a quote). After much frustration and syntax errors I stumbled upon the following statement which seems to work: select * from items where to_tsvector(name) @@ to_tsquery(E'[\']ksan') I would like to know if this is actually the correct way to search for this word? The use of brackets isn't documented anywhere that I can find so I'm not sure if it is even doing what I want it to do or if the correct result is just a coincidence. Thanks, Ryan -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
