Re: [PERFORM] First set of OSDL Shared Mem scalability results, some
Curt Sampson <[EMAIL PROTECTED]> writes: > On Sat, 23 Oct 2004, Tom Lane wrote: >> Seems to me the overhead of any such scheme would swamp the savings from >> avoiding kernel/userspace copies ... > Well, one really can't know without testing, but memory copies are > extremely expensive if they go outside of the cache. Sure, but what about all the copying from write queue to page? >> the locking issues alone would be painful. > I don't see why they would be any more painful than the current locking > issues. Because there are more locks --- the write queue data structure will need to be locked separately from the page. (Even with a separate write queue per page, there will need to be a shared data structure that allows you to allocate and find write queues, and that thing will be a subject of contention. See BufMgrLock, which is not held while actively twiddling the contents of pages, but is a serious cause of contention anyway.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] Queries slow using stored procedures
Hi, Has anybody got any ideas on my recent posting ? (thanks in advance) :- I have a problem where a query inside a function is up to 100 times slower inside a function than as a stand alone query run in psql. The column 'botnumber' is a character(10), is indexed and there are 125000 rows in the table. Help please! This query is fast:- explain analyze SELECT batchserial FROM transbatch WHERE botnumber = '1-7' LIMIT 1; QUERY PLAN Limit (cost=0.00..0.42 rows=1 width=4) (actual time=0.73..148.23 rows=1 loops=1) -> Index Scan using ind_tbatchx on transbatch (cost=0.00..18.73 rows=45 width=4) (actual time=0.73..148.22 rows=1 loops=1) Index Cond: (botnumber = '1-7'::bpchar) Total runtime: 148.29 msec(4 rows) This function is slow:- CREATE OR REPLACE FUNCTION sp_test_rod3 ( ) returns integer as 'DECLARE bot char(10); oldbatch INTEGER;BEGIN bot := ''1-7''; SELECT INTO oldbatch batchserial FROM transbatch WHERE botnumber = bot LIMIT 1; IF FOUND THEN RETURN 1; ELSE RETURN 0; END IF; END;'language plpgsql ; explain analyze SELECT sp_test_rod3(); QUERY PLAN Result (cost=0.00..0.01 rows=1 width=0) (actual time=1452.39..1452.40 rows=1 loops=1) Total runtime: 1452.42 msec(2 rows)
Re: [PERFORM] Queries slow using stored procedures
Rod Dutton wrote: Hi, Has anybody got any ideas on my recent posting ? (thanks in advance) :- I have a problem where a query inside a function is up to 100 times slower inside a function than as a stand alone query run in psql. The column 'botnumber' is a character(10), is indexed and there are 125000 rows in the table. [...] I had a similar problem before, where the function version (stored procedure or prepared query) was much slower. I had a bunch of tables all with references to another table. I was querying all of the references to see if anyone from any of the tables was referencing a particular row in the base table. It turned out that one of the child tables was referencing the same row 300,000/500,000 times. So if I happened to pick *that* number, postgres wanted to a sequential scan because of all the potential results. In my testing, I never picked that number, so it was very fast, since it knew it wouldn't get in trouble. In the case of the stored procedure, it didn't know which number I was going to ask for, so it had to plan for the worst, and *always* do a sequential scan. So the question is... In your table, does the column "botnumber" have the same value repeated many, many times, but '1-7' only occurs a few? If you change the function to: CREATE OR REPLACE FUNCTION sp_test_rod3 ( ) returns integer as ' DECLARE bot char(10); oldbatch INTEGER; BEGIN SELECT INTO oldbatch batchserial FROM transbatch WHERE botnumber = ''1-7'' LIMIT 1; IF FOUND THEN RETURN 1; ELSE RETURN 0; END IF; END; ' language plpgsql ; Is it still slow? I don't know if you could get crazy with something like: select 1 where exist(select from transbatch where botnumber = '1-7' limit 1); Just some thoughts about where *I've* found performance to change between functions versus raw SQL. You probably should also mention what version of postgres you are running (and possibly what your hardware is) John =:-> signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Queries slow using stored procedures
Rod Dutton wrote:
Thank John,
I am running Postgres 7.3.7 on a Dell PowerEdge 6600 Server with Quad Xeon
2.7GHz processors with 16GB RAM and 12 x 146GB drives in Raid 10 (OS, WAL,
Data all on separate arrays).
You might want think about upgraded to 7.4, as I know it is better at
quite a few things. But I'm not all that experienced (I just had a
similar problem).
I did try hard coding botnumber as you suggested and it was FAST. So it
does look like the scenario that you have explained.
There are 2 ways of doing it that I know of. First, you can make you
function create a query and execute it. Something like:
EXECUTE ''SELECT 1 FROM transbatch WHERE botnumber = ''
|| quote_literal(botnum)
|| '' LIMIT 1'';
That forces the database to redesign the query each time. The problem
you are having is a stored procedure has to prepare the query in advance.
does the column "botnumber" have the same value repeated many, many times,
but '1-7' only occurs a few?
Yes, that could be the case, the table fluctuates massively from small to
big to small regularly with a real mixture of occurrences of these values
i.e. some values are repeated many times and some occur only a few times.
I wonder if the answer is to: a) don't use a stored procedure b) up the
statistics gathering for that column ?
I don't believe increasing statistics will help, as prepared statements
require one-size-fits-all queries.
I will try your idea: select 1 where exist(select from transbatch where
botnumber = '1-7' limit 1);
Also, how can I get "EXPLAIN" output from the internals of the stored
procedure as that would help me?
I believe the only way to get explain is to use prepared statements
instead of stored procedures. For example:
PREPARE my_plan(char(10)) AS SELECT 1 FROM transbatch
WHERE botnumber = $1 LIMIT 1;
EXPLAIN ANALYZE EXECUTE my_plan('1-7');
Many thanks,
Rod
If you have to do the first thing I mentioned, I'm not sure if you are
getting much out of your function, so you might prefer to just ask the
question directly.
What really surprises me is that it doesn't use the index even after the
LIMIT clause. But I just did a check on my machine where I had a column
with lots of repeated entries, and it didn't use the index.
So a question for the true Guru's (like Tom Lane):
Why doesn't postgres use an indexed query if you supply a LIMIT?
John
=:->
signature.asc
Description: OpenPGP digital signature
Re: [PERFORM] Queries slow using stored procedures
Rod Dutton wrote: I also should add that the sp is only slow when the table is big (probably obvious!). Rod Sure, the problem is it is switching to a sequential search, with a lot of rows, versus doing an indexed search. It's all about trying to figure out how to fix that, especially for any value of botnum. I would have hoped that using LIMIT 1 would have fixed that. John =:-> signature.asc Description: OpenPGP digital signature
[PERFORM] Sequential Scan with LIMIT
I was looking into another problem, and I found something that surprised me. If I'm doing "SELECT * FROM mytable WHERE col = 'myval' LIMIT 1.". Now "col" is indexed, by mytable has 500,000 rows, and 'myval' occurs maybe 100,000 times. Without the LIMIT, this query should definitely do a sequential scan. But with the LIMIT, doesn't it know that it will return at max 1 value, and thus be able to use the index? It seems to be doing the LIMIT too late. The real purpose of this query is to check to see if a value exists in the column, so there might be a better way of doing it. Here is the demo info: # select count(*) from finst_t; 542315 # select count(*) from finst_t where store_id = 539960; 85076 # explain analyze select id from finst_t where store_id = 539960 limit 1; QUERY PLAN --- Limit (cost=0.00..0.13 rows=1 width=4) (actual time=860.000..860.000 rows=1 loops=1) -> Seq Scan on finst_t (cost=0.00..11884.94 rows=88217 width=4) (actual time=860.000..860.000 rows=1 loops=1) Filter: (store_id = 539960) Total runtime: 860.000 ms Notice that the "actual rows=1", meaning it is aware of the limit as it is going through the table. But for some reason the planner thinks it is going to return 88,217 rows. (This is close to the reality of 85076 if it actually had to find all of the rows). Now, if I do a select on a value that *does* only have 1 value, it works fine: # explain analyze select id from finst_t where store_id = 9605 limit 1; QUERY PLAN Limit (cost=0.00..3.96 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1) -> Index Scan using finst_t_store_id_idx on finst_t (cost=0.00..3.96 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1) Index Cond: (store_id = 9605) Total runtime: 0.000 ms And 1 further thing, I *can* force it to do a fast index scan if I disable sequential scanning. # set enable_seqscan to off; # explain analyze select id from finst_t where store_id = 539960 limit 1; QUERY PLAN Limit (cost=0.00..1.59 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1) -> Index Scan using finst_t_store_id_idx on finst_t (cost=0.00..140417.22 rows=88217 width=4) (actual time=0.000..0.000 rows=1 loops=1) Index Cond: (store_id = 539960) Total runtime: 0.000 ms Could being aware of LIMIT be added to the planner? Is there a better way to check for existence? John =:-> PS> I'm using postgres 8.0-beta3 on win32 (the latest installer). signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Sequential Scan with LIMIT
John Meinel <[EMAIL PROTECTED]> writes: > I was looking into another problem, and I found something that surprised > me. If I'm doing "SELECT * FROM mytable WHERE col = 'myval' LIMIT 1.". > Now "col" is indexed, by mytable has 500,000 rows, and 'myval' occurs > maybe 100,000 times. Without the LIMIT, this query should definitely do > a sequential scan. > But with the LIMIT, doesn't it know that it will return at max 1 value, > and thus be able to use the index? But the LIMIT will cut the cost of the seqscan case too. Given the numbers you posit above, about one row in five will have 'myval', so a seqscan can reasonably expect to hit the first matching row in the first page of the table. This is still cheaper than doing an index scan (which must require reading at least one index page plus at least one table page). The test case you are showing is probably suffering from nonrandom placement of this particular data value; which is something that the statistics we keep are too crude to detect. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Sequential Scan with LIMIT
Tom Lane wrote: John Meinel <[EMAIL PROTECTED]> writes: I was looking into another problem, and I found something that surprised me. If I'm doing "SELECT * FROM mytable WHERE col = 'myval' LIMIT 1.". Now "col" is indexed, by mytable has 500,000 rows, and 'myval' occurs maybe 100,000 times. Without the LIMIT, this query should definitely do a sequential scan. But with the LIMIT, doesn't it know that it will return at max 1 value, and thus be able to use the index? But the LIMIT will cut the cost of the seqscan case too. Given the numbers you posit above, about one row in five will have 'myval', so a seqscan can reasonably expect to hit the first matching row in the first page of the table. This is still cheaper than doing an index scan (which must require reading at least one index page plus at least one table page). The test case you are showing is probably suffering from nonrandom placement of this particular data value; which is something that the statistics we keep are too crude to detect. regards, tom lane You are correct about non-random placement. I'm a little surprised it doesn't change with values, then. For instance, # select count(*) from finst_t where store_id = 52; 13967 Still does a sequential scan for the "select id from..." query. The only value it does an index query for is 9605 which only has 1 row. It estimates ~18,000 rows, but that is still < 3% of the total data. This row corresponds to disk location where files can be found. So when a storage location fills up, generally a new one is created. This means that *generally* the numbers will be increasing as you go further in the table (not guaranteed, as there are multiple locations open at any one time). Am I better off in this case just wrapping my query with: set enable_seqscan to off; query set enable_seqscan to on; There is still the possibility that there is a better way to determine existence of a value in a column. I was wondering about something like: SELECT 1 WHERE EXISTS (SELECT id FROM finst_t WHERE store_id=52 LIMIT 1); Though the second part is the same, so it still does the sequential scan. This isn't critical, I was just trying to understand what's going on. Thanks for your help. John =:-> signature.asc Description: OpenPGP digital signature
[PERFORM] Reindexdb and REINDEX
Hi, I have had some performance problems recently on very large tables (10s of millions of rows). A vacuum full did make a large improvement and then dropping & re-creating the indexes also was very beneficial. My performance problem has now been solved. My question is: will using the contrib/reindexdb or REINDEX sql command do essentially the same job as dropping and re-creating the indexes. I.E. do you get a fully compacted and balanced index? If so then I could use contrib/reindexdb or REINDEX instead of drop/recreate. How is concurrency handled by contrib/reindexdb and REINDEX (I know you can create an index on the fly with no obvious lock outs)? Thanks, Rod
Re: [PERFORM] Reindexdb and REINDEX
"Rod Dutton" <[EMAIL PROTECTED]> writes: > My question is: will using the contrib/reindexdb or REINDEX sql command do > essentially the same job as dropping and re-creating the indexes. I.E. do > you get a fully compacted and balanced index? Yes. > How is concurrency handled by contrib/reindexdb and REINDEX (I know you can > create an index on the fly with no obvious lock outs)? In 8.0 they are almost equivalent, but in earlier releases REINDEX takes an exclusive lock on the index's parent table. The details are: DROP INDEX: takes exclusive lock, but doesn't hold it long. CREATE INDEX: takes ShareLock, which blocks writers but not readers. So when you do it that way, readers can use the table while CREATE INDEX runs, but of course they have no use of the dropped index. Putting the DROP and the CREATE in one transaction isn't a good idea if you want concurrency, because then the exclusive lock persists till transaction end. REINDEX before 8.0: takes exclusive lock for the duration. This of course is a dead loss for concurrency. REINDEX in 8.0: takes ShareLock on the table and exclusive lock on the particular index. This means that writers are blocked, but readers can proceed *as long as they don't try to use the index under reconstruction*. If they try, they block. If you're rebuilding a popular index, you have a choice of making readers do seqscans or having them block till the rebuild completes. One other point is that DROP/CREATE breaks any stored plans that use the index, which can have negative effects on plpgsql functions and PREPAREd statements. REINDEX doesn't break plans. We don't currently have any automated way of rebuilding stored plans, so in the worst case you may have to terminate open backend sessions after a DROP/CREATE. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] First set of OSDL Shared Mem scalability results, some
On Sun, 24 Oct 2004, Tom Lane wrote: > > Well, one really can't know without testing, but memory copies are > > extremely expensive if they go outside of the cache. > > Sure, but what about all the copying from write queue to page? There's a pretty big difference between few-hundred-bytes-on-write and eight-kilobytes-with-every-read memory copy. As for the queue allocation, again, I have no data to back this up, but I don't think it would be as bad as BufMgrLock. Not every page will have a write queue, and a "hot" page is only going to get one once. (If a page has a write queue, you might as well leave it with the page after flushing it, and get rid of it only when the page leaves memory.) I see the OS issues related to mapping that much memory as a much bigger potential problem. cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.NetBSD.org Make up enjoying your city life...produced by BIC CAMERA ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] First set of OSDL Shared Mem scalability results, some
Curt Sampson <[EMAIL PROTECTED]> writes: > I see the OS issues related to mapping that much memory as a much bigger > potential problem. I see potential problems everywhere I look ;-) Considering that the available numbers suggest we could win just a few percent (and that's assuming that all this extra mechanism has zero cost), I can't believe that the project is worth spending manpower on. There is a lot of much more attractive fruit hanging at lower levels. The bitmap-indexing stuff that was recently being discussed, for instance, would certainly take less effort than this; it would create no new portability issues; and at least for the queries where it helps, it could offer integer-multiple speedups, not percentage points. My engineering professors taught me that you put large effort where you have a chance at large rewards. Converting PG to mmap doesn't seem to meet that test, even if I believed it would work. regards, tom lane ---(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] First set of OSDL Shared Mem scalability results, some
On Sun, 24 Oct 2004, Tom Lane wrote: > Considering that the available numbers suggest we could win just a few > percent... I must confess that I was completely unaware of these "numbers." Where do I find them? cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.NetBSD.org Make up enjoying your city life...produced by BIC CAMERA ---(end of broadcast)--- TIP 8: explain analyze is your friend
