Re: [PERFORM] function call vs staright query
Hi all again. I've seen that there has been another post similar to mine: http://archives.postgresql.org/pgsql-performance/2007-03/msg00166.php I understand that the query planner has less infos about the query at the time the function is defined / loaded. In my case in the query there is a field like string expression that seems to be the performance killer. If the string is 'SOMETING%' the straight query is fast. While '%SOMETING%' makes the straight query be as slow as the function. This thing clears part of the problem. The EXPLAIN actually explains a lot. But the details should be complete at call time when the pattern string is known. So at least the first case should have comparable performances for both the straight query and the function call. So my previous question becomes: How can I delay the query planner decisions until the actual query is to be done inside the function body? Many thanks again for any hint. On Sunday 18 March 2007 07:50 Vincenzo Romano wrote: Hi all. I'm experiencing a strange behaviour with 8.1.8 (cannot do upgrades to 8.2 at the moment). On a 13+ million rows table I can do a query with results back in less than 100 ms. Result is a set of bigint. But when I encapsulate that query into an SQL function with three parameters the results come back in about one minute. The function contains just the same query as above. Of course there's been no change in indices or even into the table itself between the two tests. I'm almost sure I'm missing something, but have no clue about what! Any hint? -- Vincenzo Romano Maybe Computers will never become as intelligent as Humans. For sure they won't ever become so stupid. [VR-1987] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] text equality worse than pattern matching (v8.1.8)
I'm running in some weird (IMHO) bahviour.
When I search a table for certain text (equality est on the relelvant field)
it takes much more time than doing the same test by adding a trailing '%' and
using the LIKE operator.
With much more I mean 1000+ times slower.
This is the table (sorry for the Italian strings):
| PSQL |
noa=# \d ts_t_records
Tabella public.ts_t_records
Colonna| Tipo |
Modificatori
---+--+--
fiel_uniqueid | bigint | not null
item_uniqueid | bigint | not null
reco_alphanum | text | not null default ''::text
reco_floating | double precision | default 0.0
reco_integral | bigint | default 0
reco_timedate | timestamp with time zone | default now()
reco_isactive | boolean | default true
reco_effectiv | timestamp with time zone | default '-infinity'::timestamp
with time zone
reco_uniqueid | bigint | not null default
nextval('ts_t_records_reco_uniqueid_seq'::regclass)
Indici:
ts_i_records_0 btree (item_uniqueid)
ts_i_records_1 btree (reco_uniqueid)
ts_i_records_2 btree (reco_isactive, reco_effectiv)
ts_i_records_3 btree (reco_alphanum)
ts_i_records_4 btree (fiel_uniqueid)
| /PSQL |
And these are the EXPLAINs for the queries:
| PSQL |
noa=# EXPLAIN SELECT * FROM ts_t_records WHERE fiel_uniqueid=2 AND
reco_alphanum='TEST' AND reco_isactive AND reco_effectiv=NOW();
QUERY PLAN
--
Bitmap Heap Scan on ts_t_records (cost=5110.50..6191.86 rows=277 width=65)
Recheck Cond: ((reco_alphanum = 'TEST'::text) AND (fiel_uniqueid = 2))
Filter: (reco_isactive AND (reco_effectiv = now()))
- BitmapAnd (cost=5110.50..5110.50 rows=277 width=0)
- Bitmap Index Scan on ts_i_records_3 (cost=0.00..36.32 rows=5234
width=0)
Index Cond: (reco_alphanum = 'TEST'::text)
- Bitmap Index Scan on ts_irecords_4 (cost=0.00..5073.93
rows=812550 width=0)
Index Cond: (fiel_uniqueid = 2)
(8 righe)
noa=# EXPLAIN SELECT * FROM ts_t_records WHERE fiel_uniqueid=2 AND
reco_alphanum LIKE 'TEST%' AND reco_isactive AND reco_effectiv=NOW();
QUERY PLAN
-
Index Scan using ts_i_records_3 on ts_t_records (cost=0.00..6.01 rows=1
width=65)
Index Cond: ((reco_alphanum = 'TEST'::text) AND (reco_alphanum
'TESU'::text))
Filter: ((fiel_uniqueid = 2) AND (reco_alphanum ~~ 'TEST%'::text) AND
reco_isactive AND (reco_effectiv = now()))
(3 righe)
| /PSQL |
Not only are query plans very different, but the equality query is much worse
than the pattern matching one.
In my (maybe wrong) mind I expected the reverse.
What's wrong with the my expectations? Am I missing something?
MTIA.
--
Vincenzo Romano
Maybe Computers will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1987]
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] text equality worse than pattern matching (v8.1.8)
On 3/18/07, Vincenzo Romano [EMAIL PROTECTED] wrote: And these are the EXPLAINs for the queries: please provide output of explain analyze of the queries. otherwise - it is not really useful. depesz ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Performance Tuning and Disk Cache
I have a very slow query that I'm trying to tune. I think my performance tuning is being complicated by the system's page cache. If a run the query after the system has been busy with other tasks for quite a long time then the query can take up to 8-10 minutes to complete. If I then rerun the same query it will complete in a couple of seconds. Does anyone know how I can repeatedly run the same query in the worst case scenario of no postgres data in the disk cache (e.g., clear the page cache or force it to be ignored)? Thanks for any help. Barry ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance Tuning and Disk Cache
On 3/18/07, Barry Moore [EMAIL PROTECTED] wrote: Does anyone know how I can repeatedly run the same query in the worst case scenario of no postgres data in the disk cache (e.g., clear the page cache or force it to be ignored)? try to disconnect from postgresql, reconnect, rerun the query. if it doesn't help - you can try unmounting filesystem which contains postgresql data, and remounting it again. of course with postgresql shutdown. depesz -- http://www.depesz.com/ - nowy, lepszy depesz ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Performance Tuning and Disk Cache
Barry Moore wrote: I have a very slow query that I'm trying to tune. I think my performance tuning is being complicated by the system's page cache. If a run the query after the system has been busy with other tasks for quite a long time then the query can take up to 8-10 minutes to complete. If I then rerun the same query it will complete in a couple of seconds. Does anyone know how I can repeatedly run the same query in the worst case scenario of no postgres data in the disk cache (e.g., clear the page cache or force it to be ignored)? In my experience the only 100% reliable way to do this is to reboot the machine. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance Tuning and Disk Cache
If you are running on a Linux kernel, try /proc/sys/vm/drop_caches. I believe the appropriate command is echo 3 /proc/sys/vm/drop_caches. Since Postgres has its own cache of data, the above followed by a PG restart should do what you are looking for. Ranga Barry Moore wrote: I have a very slow query that I'm trying to tune. I think my performance tuning is being complicated by the system's page cache. If a run the query after the system has been busy with other tasks for quite a long time then the query can take up to 8-10 minutes to complete. If I then rerun the same query it will complete in a couple of seconds. Does anyone know how I can repeatedly run the same query in the worst case scenario of no postgres data in the disk cache (e.g., clear the page cache or force it to be ignored)? In my experience the only 100% reliable way to do this is to reboot the machine. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance Tuning and Disk Cache
On Sun, Mar 18, 2007 at 06:45:34AM -0600, Barry Moore wrote: Does anyone know how I can repeatedly run the same query in the worst case scenario of no postgres data in the disk cache (e.g., clear the page cache or force it to be ignored)? Depends on your OS. On linux you can run: echo 1 /proc/sys/vm/drop_caches Mike Stone ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] function call vs staright query
Vincenzo Romano [EMAIL PROTECTED] writes: How can I delay the query planner decisions until the actual query is to be done inside the function body? Use plpgsql's EXECUTE. AFAIR there is no way in a SQL-language function. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
