[PERFORM] full text search - dictionary caching
Hello Does Postgres have ability to keep .dict and .affix files cached globally for all client sessions? Every time I connect to test server - it takes 3 seconds to load 4MB dictionary when executing first FTS query. -- Regards, Tomasz Myrta -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow SQL query (14-15 seconds)
Bruno Baguette napisal 13.11.2008 12:02: Hello ! Sorry for the subject, I didn't found a better one ! :-/ I'm having a problem with this query (below) that takes betweend 14 and 15 seconds to run, which is too long for the end-user. I've done a EXPLAIN ANALYZE (below below) but I'm having difficulties to see which part of that query is taking so many times. If the lines are too long, your mailreader may cut them and make the SQL query and the query plan unreadable, so I've put a copy of them on pastebin.com : http://pastebin.com/m53ca365 Can you give me some tips to see which part of the query is guilty ? 1. Your explain analyze points to a lot of loops in exists clause: Filter: ((NOT is_deleted) AND (subplan)) 16.5msec * 800loops = ~13sec. Try to replace exists() with in() or inner joins/distinct. 2. Those 3 left joins can be replaced with subselects: select (select count(*)... ) as societe_nbre_commandes from societes ... -- Regards, Tomasz Myrta -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] cacheable stored functions?
Dnia 2004-02-20 16:35, Uytkownik Bill Moran napisa: Can anyone say whether this is a supported feature in plpgsql, and is safe to use? Is it simply undocumented, or am I just looking in the wrong place? iscachable is only for backward compatibility - it's changed now to IMMUTABLE You can read more about immutable, stable and volatile functions in Postgresql documentation - chapter SQL Commands/CREATE FUNCTION. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Overlaping indexes
Dnia 2004-02-02 15:46, Uytkownik Rigmor Ukuhe napisa: Hi, I have many indexes somehow overlaping like: ... btree (STATUS, VISIBLE, NP_ID); ... btree (STATUS, VISIBLE); is perfomance gained by more exact index worth overhead with managing indexes. The second (2 columns) index is useless - it's function is well done by the first one (3 columns). Regards, Tomasz Myrta ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Overlaping indexes
Dnia 2004-02-02 19:30, Uytkownik scott.marlowe napisa: Not entirely, since it only has to sort two columns, it will be smaller, and will therefore be somewhat faster. Can you say something more about it? Will it be enough faster to keep them both? Did anyone make such tests? Regards, Tomasz Myrta ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Queries with timestamp II
Dnia 2004-01-26 15:12, Uytkownik Arnau napisa:
Hi all,
First of all thanks to Josh and Richard for their replies. What I have
done to test
their indications is the following. I have created a new table identical to
STATISTICS,
and an index over the TIMESTAMP_IN field.
CREATE TABLE STATISTICS2
(
STATISTIC_IDNUMERIC(10) NOT NULL DEFAULT
NEXTVAL('STATISTIC_ID_SEQ')
CONSTRAINT pk_st_statistic2_id PRIMARY KEY,
TIMESTAMP_INTIMESTAMP,
VALUE NUMERIC(10)
);
Do you really have to use numeric as primary key? Integer datatypes
(int4/int8) are much faster than numeric.
CREATE INDEX i_stats2_tin ON STATISTICS2(TIMESTAMP_IN);
After that I inserted the data from STATISTICS and vacuumed the DB:
INSERT INTO STATISTICS2 ( SELECT * FROM STATISTICS );
vacuumdb -f -z -d test
once the vacuum has finished I do the following query
explain analyze select * from statistics2 where timestamp_in
to_timestamp( '20031201', 'MMDD' );
NOTICE: QUERY PLAN:
Seq Scan on statistics2 (cost=0.00..638.00 rows=9289 width=35) (actual
time=0.41..688.34 rows=27867 loops=1)
Total runtime: 730.82 msec
That query is not using the index. Anybody knows what I'm doing wrong?
Over 25000 rows match your condition:
timestamp_in to_timestamp( '20031201', 'MMDD' );
How many rows do you have in your table? It's possible, that seq scan is
just faster than using index when getting so many rows output.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [PERFORM] Is This My Speed Limit?
Hi! It's just my curiosity. I wonder if there is any way to break my speed limit on AMD 450Mhz: Hash Join (cost=189.79..1508.67 rows=11203 width=48) (actual time=129.20..1780.53 rows=9912 loops=1) Hash Join (cost=208.74..1751.68 rows=11203 width=58) (actual time=135.87..1113.69 rows=9912 loops=1) Well, it looks like a speed limit. I wouldn't expect better speed for queries returning 1 rows. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Index problem
Hi,
I have a table containing columns:
END_DATE timestamptz NOT NULL
REO_ID int4 NOT NULL
and i am indexed REO_ID coulumn.
I have a query:
select REO_ID, END_DATE from PRIORITY_STATISTICS where REO_ID IN
('112851' ,'112859' ,'112871' ,'112883' ,'112891' ,'112904' ,'112915'
,'112924' ,'112939' ,'112947' ,'112960' ,'112984' ,'112999' ,'113013'
,'113032' ,'113059' ,'113067' ,'113084' ,'113096' ,'113103' ,'113110'
,'113117' ,'113125' ,'113132' ,'113139' ,'113146' ,'113153' ,'113160'
,'113167' ,'113174' ,'113181' ,'113188' ,'113195' ,'113204' ,'113268'
,'113279' ,'113294' ,'113302' ,'113317' ,'113340' ,'113358' ,'113385'
,'113404' ,'113412' ,'113419' ,'113429' ,'113436' ,'113443' ,'113571'
,'113636' ,'113649' ,'113689' ,'113705' ,'113744' ,'113755' ,'113724'
,'113737' ,'113812' ,'113828' ,'113762' ,'113842' ,'113869' ,'113925'
,'113976' ,'114035' ,'114044' ,'114057' ,'114070' ,'114084' ,'114094'
,'114119' )
and it is _not_ using that index
But following query (notice there are less id-s in WHERE clause, but rest is
same)
select REO_ID, END_DATE from PRIORITY_STATISTICS where REO_ID IN
('112851' ,'112859' ,'112871' ,'112883' ,'112891' ,'112904' ,'112915'
,'112924' ,'112939' ,'112947' ,'112960' ,'112984' ,'112999' ,'113013'
,'113032' ,'113059' ,'113067' ,'113084' ,'113096' ,'113103' ,'113110'
,'113117' ,'113125' ,'113132' ,'113139' ,'113146' ,'113153' ,'113160'
,'113167' ,'113174' ,'113181' ,'113188' ,'113195' ,'113204' ,'113268'
,'113279' ,'113294' ,'113302' ,'113317' ,'113340' ,'113358' ,'113385'
,'113404' ,'113412' ,'113419' ,'113429' ,'113436' ,'113443' ,'113571'
,'113636' ,'113649' ,'113689' ,'113705' ,'113744' ,'113755' ,'113724'
,'113737' )
will _is_ using index:
Why not. It's just because the second query is more selective. Probably
you don't have too many rows in your table and Postgres thinks it's
better (faster) to use sequential scan than index one.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Indexing question
So if I understand correctly I could ditch the 'deleted' field entirely and use just the 'deleteddate' field. This 'deleteddate' field would be NULL by default. It would contain a date value if the record is considered 'deleted'. The index would be 'create index a on tablename(deleteddate) where deleteddate is null'. I could then access 'current' records with a view like 'create view x_view as select * from tablename where deleteddate is null'. Is that correct? This would be the best performing solution for this kind of thing, I think (theoretically at least)? Kind regards, Alexander Priem. Near, but not exactly. You don't need field deleted - it's true. Your example: create index a on tablename(deleteddate) where deleteddate is null we can translate to: create index a on tablename(NULL) where deleteddate is null which doesn't make too much sense. Check your queries. You probably have something like this: select * from tablename where not deleted and xxx Create your index to match xxx clause - if xxx is some_id=13, then create your index as: create index on tablename(some_id) where deleteddate is null; Regards, Tomasz Myrta ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Indexing question
create index orad_id_index on orderadvice (orad_id) where orad_deleteddate is null; create index orad_name_index on orderadvice (orad_name) where orad_deleteddate is null; create view orderadvice_edit as select orad_id,orad_name,orad_description,orad_value,orad_value_quan from orderadvice where orad_deleteddate is null; Would queries like 'select * from orderadvice_edit where orad_id=100' or 'select * from orderadvice_edit order by orad_name' both use one of these two partial indexes, given enough records are present in the table? There would be a double index on the primary key this way, right? It looks much better now. I'm not sure about the second index. Probably it will be useless, because you sort ALL records with deleteddtata is null. Maybe the first index will be enough. I'm not sure what to do with doubled index on a primary key field. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 8: explain analyze is your friend
