Alex,
looks like you need to read documentation and tune postgresql.conf.
Currently, it looks not good. I have no time to guide you, so search
archives for tuning postgresql.conf. This was discussed a lot of time.
Oleg
On Wed, 4 Feb 2009, Alex Neth wrote:
I've also found other queries that were really fast with MySQL are really
slow in Postgres. I'm hoping that is a matter of tuning. Overall I'm
finding the query times to be extremely unpredictable.
I added a slow query logger to my application that also does an explain.
Check these out. The time in parens is the time for the initial execution
(before it was cached). These are not under heavy load. Note that there are
around 400 users - not a lot. 22 seconds is ridiculous.
Slow query: (22.0198369026184) [0] SELECT * FROM "users" WHERE
("users"."remember_token" = E'26a2b54d2b2e18edc8aa2e1f0e0508b0d858d650')
LIMIT 1
Limit (cost=0.00..33.17 rows=1 width=784) (actual time=0.182..0.183 rows=1
loops=1)
-> Seq Scan on users (cost=0.00..33.17 rows=1 width=784) (actual
time=0.181..0.181 rows=1 loops=1)
Filter: ((remember_token)::text =
'26a2b54d2b2e18edc8aa2e1f0e0508b0d858d650'::text)
Total runtime: 0.223 ms
Slow query: (59.8780090808868) [0] SELECT id FROM "source_listings" WHERE
(post_time BETWEEN '2009-01-27 08:14:58.261978' AND '2009-02-05
08:14:58.262034' AND ((geo_lon BETWEEN 5751555 AND 5759369 AND geo_lat
BETWEEN 12773379 AND 12776908))) ORDER BY post_time DESC LIMIT 60 OFFSET 0
Limit (cost=89.38..89.38 rows=1 width=12) (actual time=1368.555..1368.644
rows=60 loops=1) -> Sort (cost=89.38..89.38 rows=1 width=12) (actual
time=1368.552..1368.588 rows=60 loops=1)
Sort Key: post_time Sort Method: top-N heapsort Memory: 19kB
-> Index Scan using x_sl_lat_lon_pt_br_lt_region on source_listings
(cost=0.00..89.37 rows=1 width=12) (actual time=0.097..1365.469 rows=2078
loops=1)
Index Cond: ((geo_lat >= 12773379) AND (geo_lat <= 12776908) AND
(geo_lon >= 5751555) AND (geo_lon
<= 5759369) AND (post_time >= '2009-01-27 08:14:58.261978'::timestamp without
time zone) AND (post_time <= '2009-02-05 08:14:58.262034'::timestamp without
time zone))Total runtime: 1368.722 ms
On Feb 3, 2009, at 8:15 PM, Oleg Bartunov wrote:
Alex, can you somehow identify document, which has problem with
long word errors ? Also, if you have space on disk I'd recommend to try
select *, to_tsvector('english',full_listing) as flv from source_listings;
This is equally slow.
I don't remember if you said us information about
your setup (pg version, OS, memory, what did you change in
postgresql.conf..)
Version is 8.3.5. Ubuntu 2.6.21. 2Gb RAM. postgresql.conf changes:
shared_buffers = 24MB # min 128kB or max_connections*16kB
work_mem = 10MB # min 64kB
max_fsm_pages = 153600 # min max_fsm_relations*16, 6 bytes
each
Oleg
On Tue, 3 Feb 2009, Alex Neth wrote:
Based on suggestions from this list, I am trying to create a tsvector
column and index that, since it is perhaps the recheck and rebuilding of
all the vectors that is slowing things down. I don't understand why a
recheck is necessary on a gin index.....
My update statement has been running for 36 hours now and has not
finished. The statement is: update source_listings set flv =
to_tsvector('english', full_listing); I know that it is still working
because it occasionally prints out one of those long word errors.
I have only 1.6M rows and each entry in that column is a standard size web
page with just the text, maybe 3-5K.
For sure I don't have meaningful long words. Perhaps that is because it
is not handling the HTML well and I should be parsing down the web page
first. Hopefully that doesn't mean I need to rebuild this column over the
course of 3 days - I didn't expect it to take this long so I thought I'd
just try it out.
On Feb 2, 2009, at 1:49 AM, Oleg Bartunov wrote:
Alex,
what text you're indexing ? I don't believe you have meaningful
very long words ( > 2047 characters).
Do you really need multicolumn index ?
I'd recommend to separate problem - create column fts for
tsvector('english',full_listing), create index on it and try full-text
query. The way you're doing imply calling to_tsvector every time you
search, which can be very costly.
Olegk
On Sun, 1 Feb 2009, Alex wrote:
So this seems to be because the result size is too big. I still don't
know why it is looping through every record and printing a warning,
but adding a LIMIT makes the queries complete in a reasonable time
(although not all that fast).
However I need to sort and also have many other facets that may or may
not be included in the query. Adding a sort makes it load every
record again and take forever.
I tried to create an index including all of the fields I query on to
see if that would work, but I get an error the the index row is too
large:
=> create index master_index on source_listings(geo_lat, geo_lon,
price, bedrooms, region, city, listing_type, to_tsvector('english',
full_listing), post_time);
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
ERROR: index row requires 13356 bytes, maximum size is 8191
Any ideas about how to resolve this?
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@xxxxxxxxxx, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@xxxxxxxxxx, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@xxxxxxxxxx, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
