Hi,
I've developed a system that was not to work online, but now it is
online and it is degrading due to bad design choices.
Here is the thing. I've the database build in "vertical mode". I will
justo explain what I mean with that.
Attribute | Value
site_name | Some site name1
uri | Some uri1
job_title | Some job title1
job_description | Some job description1
country_ad | Some country1
location_ad | Some location 1
The above is the "vertical mode" that I was talking about. It is
actually more complicated because it deals with JOINS. The SELECT
below is the SELECT that I use to rotate the data.
[query]
SELECT
md5(site_name.uri) as hash,
NULLIF(site_name.site_name, ''::text) AS site_name,
site_name.uri::text AS uri,
NULLIF(job_title.job_title, ''::text) AS job_title,
NULLIF(job_description.job_description, ''::text) AS job_description,
NULLIF(country_ad.country_ad, ''::text) AS country_ad,
NULLIF(zone_ad.zone_ad, ''::text) AS zone_ad,
NULLIF(location_ad.location_ad, ''::text) AS location_ad,
date_inserted.date_inserted
FROM
((((((tdir_uris date_inserted JOIN (
SELECT tdir_uris_text.id_category, tdir_uris_text.uri,
tdir_uris_text.n_text AS site_name -- Ter em atencao, este select tem
aqui metido o id_category p usar no WHERE final deste select
FROM tdir_uris_text
WHERE ((tdir_uris_text.id_data)::text = 'site_name'::text)
) site_name ON (((site_name.uri)::text =
(date_inserted.uri)::text))) LEFT JOIN (
SELECT tdir_uris_text.uri, tdir_uris_text.n_text AS job_title
FROM tdir_uris_text
WHERE ((tdir_uris_text.id_data)::text = 'job_title'::text)
) job_title ON (((job_title.uri)::text = (site_name.uri)::text))) LEFT JOIN (
SELECT tdir_uris_text.uri, tdir_uris_text.n_text AS job_description
FROM tdir_uris_text
WHERE ((tdir_uris_text.id_data)::text = 'job_description'::text)
) job_description ON (((job_description.uri)::text =
(site_name.uri)::text))) LEFT JOIN (
SELECT tdir_uris_text.uri, tdir_uris_text.n_text AS country_ad
FROM tdir_uris_text
WHERE ((tdir_uris_text.id_data)::text = 'country_ad'::text)
) country_ad ON (((country_ad.uri)::text =
(site_name.uri)::text))) LEFT JOIN (
SELECT tdir_uris_text.uri, tdir_uris_text.n_text AS zone_ad
FROM tdir_uris_text
WHERE ((tdir_uris_text.id_data)::text = 'zone_ad'::text)
) zone_ad ON (((zone_ad.uri)::text = (site_name.uri)::text))) LEFT JOIN (
SELECT tdir_uris_text.uri, tdir_uris_text.n_text AS location_ad
FROM tdir_uris_text
WHERE ((tdir_uris_text.id_data)::text = 'location_ad'::text)
) location_ad ON (((location_ad.uri)::text = (site_name.uri)::text)))
WHERE
site_name.id_category = 5
[/query]
With the SELECT I build a VIEW to show me something like this:
site_name | uri | job_title |
job_description | country_ad | location_ad
Some site name1 | Some uri1 | Some job title1 | Some job
description1 | Some country1 | Some location 1
Some site name2 | Some uri2 | Some job title2 | Some job
description2 | Some country2 | Some location 2
Some site name3 | Some uri3 | Some job title3 | Some job
description3 | Some country3 | Some location 3
Some site name4 | Some uri4 | Some job title4 | Some job
description4 | Some country4 | Some location 4
Some site name5 | Some uri5 | Some job title5 | Some job
description5 | Some country5 | Some location 5
My question is, how can I get the queries faster. It is possible to do
it with INDEXES or it is better to search for other approach? I simple
SELECT using a LIMIT do paginate is taking 5 minutes. Any ideas on
where to start?
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
