[PERFORM] Index not used with or condition
Hi all, I am facing a strange problem when I run EXPLAIN against a table having more than 10 records. The query have lot of OR conditions and when parts of the query is removed it is using index. To analyse it I created a table with a single column, inserted 10 records(random number) in it created index and run a query which returns 1 record which have no or condition and it was using index. I added an OR conditon and is using sequential scan. I set the enable_seqscan to off. I ran the tests again and is using index scan. So which one I have to use. Is this any bug in Explain. rgds Antony Paul. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Index not used with or condition
On more investigation I found that index scan is not used if the query have a function in it like lower() and an index exist for lower() column. rgds Antony Paul On Mon, 7 Feb 2005 14:37:15 +0530, Antony Paul <[EMAIL PROTECTED]> wrote: > Hi all, > I am facing a strange problem when I run EXPLAIN against a table > having more than 10 records. The query have lot of OR conditions > and when parts of the query is removed it is using index. To analyse > it I created a table with a single column, inserted 10 > records(random number) in it created index and run a query which > returns 1 record which have no or condition and it was using index. I > added an OR conditon and is using sequential scan. I set the > enable_seqscan to off. I ran the tests again and is using index scan. >So which one I have to use. Is this any bug in Explain. > > rgds > Antony Paul. > ---(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
[PERFORM] Is this possible / slow performance?
Hi every one, Why does this take forever (each query is sub second when done seperately)? Is it because I cannot open two cursors in the same transaction? begin; declare SQL_CUR01 cursor for SELECT A.ordernummer, B.klantnummer FROM "orders" A LEFT OUTER JOIN "klt_alg" B ON A.Klantnummer=B.Klantnummer ORDER BY A.klantnummer; fetch 100 in SQL_CUR01; declare SQL_CUR02 cursor for SELECT A.ordernummer, B.klantnummer FROM "orders" A LEFT OUTER JOIN "klt_alg" B ON A.Klantnummer=B.Klantnummer ORDER BY A.klantnummer desc; fetch 100 in SQL_CUR02; commit; TIA Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Index not used with or condition
On Mon, Feb 07, 2005 at 04:44:07PM +0530, Antony Paul wrote: > On more investigation I found that index scan is not used if the query > have a function in it like lower() and an index exist for lower() > column. What version are you using? 8.0 had fixes for this situation. /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Index not used with or condition
It depends on many circumstances, but, at first, simple question: Did you run vacuum analyze? I am satisfied with functional indexes - it works in my pg 7.4.x. Antony Paul wrote: On more investigation I found that index scan is not used if the query have a function in it like lower() and an index exist for lower() column. rgds Antony Paul On Mon, 7 Feb 2005 14:37:15 +0530, Antony Paul <[EMAIL PROTECTED]> wrote: Hi all, I am facing a strange problem when I run EXPLAIN against a table having more than 10 records. The query have lot of OR conditions and when parts of the query is removed it is using index. To analyse it I created a table with a single column, inserted 10 records(random number) in it created index and run a query which returns 1 record which have no or condition and it was using index. I added an OR conditon and is using sequential scan. I set the enable_seqscan to off. I ran the tests again and is using index scan. So which one I have to use. Is this any bug in Explain. rgds Antony Paul. ---(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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Index not used with or condition
Sorry I forgot to mention it. I am using 7.3.3. I will try it in 8.0.0 rgds Antony Paul On Mon, 7 Feb 2005 12:46:05 +0100, Steinar H. Gunderson <[EMAIL PROTECTED]> wrote: > On Mon, Feb 07, 2005 at 04:44:07PM +0530, Antony Paul wrote: > > On more investigation I found that index scan is not used if the query > > have a function in it like lower() and an index exist for lower() > > column. > > What version are you using? 8.0 had fixes for this situation. > > /* Steinar */ > -- > Homepage: http://www.sesse.net/ > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Index not used with or condition
I ran analyze; several times. rgds Antony Paul On Mon, 07 Feb 2005 12:53:30 +0100, Jan Poslusny wrote: > It depends on many circumstances, but, at first, simple question: Did > you run vacuum analyze? > I am satisfied with functional indexes - it works in my pg 7.4.x. > > Antony Paul wrote: > > >On more investigation I found that index scan is not used if the query > >have a function in it like lower() and an index exist for lower() > >column. > > > >rgds > >Antony Paul > > > > > >On Mon, 7 Feb 2005 14:37:15 +0530, Antony Paul <[EMAIL PROTECTED]> wrote: > > > > > >>Hi all, > >>I am facing a strange problem when I run EXPLAIN against a table > >>having more than 10 records. The query have lot of OR conditions > >>and when parts of the query is removed it is using index. To analyse > >>it I created a table with a single column, inserted 10 > >>records(random number) in it created index and run a query which > >>returns 1 record which have no or condition and it was using index. I > >>added an OR conditon and is using sequential scan. I set the > >>enable_seqscan to off. I ran the tests again and is using index scan. > >> So which one I have to use. Is this any bug in Explain. > >> > >>rgds > >>Antony Paul. > >> > >> > >> > > > >---(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 > > > > > > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Bad query optimizer misestimation because of TOAST
Hi, Tom, Tom Lane schrieb: > Markus Schaber <[EMAIL PROTECTED]> writes: >> [Query optimizer misestimation using lossy GIST on TOASTed columns] > > What I would be inclined to do is to extend ANALYZE to make an estimate > of the extent of toasting of every toastable column, and then modify > cost_qual_eval to charge a nonzero cost for evaluation of Vars that are > potentially toasted. What to do now? To fix this issue seems to be a rather long-term job. Is it enough to document workarounds (as in PostGIS), provided that there are such workarounds for other GIST users? Is there a bug tracking system we could file the problem, so it does not get lost? Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Can the V7.3 EXPLAIN ANALYZE be trusted?
Hi, @all, Greg Stark schrieb: > "Leeuw van der, Tim" <[EMAIL PROTECTED]> writes: > >>I don't think EXPLAIN ANALYZE puts that much overhead on a query. > > EXPLAIN ANALYZE does indeed impose a significant overhead. Additional note: In some rare cases, you can experience just the opposite effect, explain analyze can be quicker then the actual query. This is the case for rather expensive send/output functions, like the PostGIS ones: lwgeom=# \timing Zeitmessung ist an. lwgeom=# explain analyze select setsrid(geom,4326) from adminbndy1; QUERY PLAN --- Seq Scan on adminbndy1 (cost=0.00..4.04 rows=83 width=89) (actual time=11.793..2170.184 rows=83 loops=1) Total runtime: 2170.834 ms (2 Zeilen) Zeit: 2171,688 ms lwgeom=# \o /dev/null lwgeom=# select setsrid(geom,4326) from adminbndy1; Zeit: 9681,001 ms BTW: I use the cheap setsrid(geom,4326) to force deTOASTing of the geometry column. Not using it seems to ignore TOASTed columns in sequential scan simulation.) lwgeom=# explain analyze select geom from adminbndy1; QUERY PLAN --- Seq Scan on adminbndy1 (cost=0.00..3.83 rows=83 width=89) (actual time=0.089..0.499 rows=83 loops=1) Total runtime: 0.820 ms (2 Zeilen) Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com signature.asc Description: OpenPGP digital signature
[PERFORM] Retry: Is this possible / slow performance?
Hi all,
A retry of the question asked before. All tables freshly vacuumed an analized.
Two queries: one with "set enable_seqscan = on" , the other with "set
enable_seqscan = off". The first query lasts 59403 ms, the second query 31 ms (
the desc order variant has the same large difference: 122494 ms vs. 1297 ms).
(for the query plans see below).
Can I, without changing the SQL (because it is generated by a tool) or
explicitely setting "set enable_seqscan = off" for this query, trick PostgreSQL
in taking the fast variant of the queryplan?
TIA
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: [EMAIL PROTECTED]
web: www.askesis.nl
--- Query 1
begin;
set enable_seqscan = on;
declare SQL_CUR01 cursor for
SELECT A.ordernummer, B.klantnummer FROM "orders" A LEFT OUTER JOIN "klt_alg" B
ON A.Klantnummer=B.Klantnummer
ORDER BY A.klantnummer;
fetch 100 in SQL_CUR01;
commit;
QUERY PLAN
Sort (cost=259968.77..262729.72 rows=1104380 width=12)
Sort Key: a.klantnummer, a.ordernummer
-> Hash Left Join (cost=42818.43..126847.70 rows=1104380 width=12)
Hash Cond: ("outer".klantnummer = "inner".klantnummer)
-> Seq Scan on orders a (cost=0.00..46530.79 rows=1104379 width=8)
-> Hash (cost=40635.14..40635.14 rows=368914 width=4)
-> Seq Scan on klt_alg b (cost=0.00..40635.14 rows=368914
width=4)
Actual running time: 59403 ms.
--- Query 2
begin;
set enable_seqscan = off;
declare SQL_CUR01 cursor for
SELECT A.ordernummer, B.klantnummer FROM "orders" A LEFT OUTER JOIN "klt_alg" B
ON A.Klantnummer=B.Klantnummer
ORDER BY A.klantnummer;
fetch 100 in SQL_CUR01;
commit;
QUERY PLAN
Merge Left Join (cost=0.00..2586604.86 rows=1104380 width=12)
Merge Cond: ("outer".klantnummer = "inner".klantnummer)
-> Index Scan using orders_klantnummer on orders a (cost=0.00..2435790.17
rows=1104379 width=8)
-> Index Scan using klt_alg_klantnummer on klt_alg b (cost=0.00..44909.11
rows=368914 width=4)
Actual running time: 31 ms.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PERFORM] Retry: Is this possible / slow performance?
"Joost Kraaijeveld" <[EMAIL PROTECTED]> writes: > Two queries: one with "set enable_seqscan = on" , the other with "set > enable_seqscan = off". The first query lasts 59403 ms, the second query 31 ms > ( the desc order variant has the same large difference: 122494 ms vs. 1297 > ms). (for the query plans see below). The reason for the difference is that the mergejoin plan has a much lower startup cost than the hash plan, and since you're only fetching 100 rows the startup cost is dominant. IIRC the planner does make some allowance for this effect when preparing a DECLARE CURSOR plan (ie, it puts some weight on startup cost rather than considering only total cost) ... but it's not so optimistic as to assume that you only want 100 out of an estimated 1 million+ result rows. The best solution is probably to put a LIMIT into the DECLARE CURSOR, so that the planner can see how much you intend to fetch. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Are JOINs allowed with DELETE FROM
Steven Rosenstein wrote: > > > > Hi Michael, > > Thank you for the link to the documentation page. I forgot to mention that > we're still using version 7.3. When I checked the 7.3 documentation for > DELETE, there was no mention of being able to use fields from different > tables in a WHERE clause. This feature must have been added in a > subsequent release of PostgreSQL. > > Gaetano & John: I *did* try your suggestion. However, there were so many > summary ID's returned (9810 to be exact) that the DELETE seemed to be > taking forever. 7.3 is affected by bad performances if you use IN. Transform the IN in an EXIST construct. If it'is an option for you upgrade you DB engine. Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Retry: Is this possible / slow performance?
Does the planner also take into account that the Hash Join will need a huge temporary space which will exist for the whole length of the cursor existence (which may be quite long if he intends to fetch everything), whereas the Merge Join should need very little space as it is sending the rows as it fetches them using the Indexes ? On Mon, 07 Feb 2005 12:03:56 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: "Joost Kraaijeveld" <[EMAIL PROTECTED]> writes: Two queries: one with "set enable_seqscan = on" , the other with "set enable_seqscan = off". The first query lasts 59403 ms, the second query 31 ms ( the desc order variant has the same large difference: 122494 ms vs. 1297 ms). (for the query plans see below). The reason for the difference is that the mergejoin plan has a much lower startup cost than the hash plan, and since you're only fetching 100 rows the startup cost is dominant. IIRC the planner does make some allowance for this effect when preparing a DECLARE CURSOR plan (ie, it puts some weight on startup cost rather than considering only total cost) ... but it's not so optimistic as to assume that you only want 100 out of an estimated 1 million+ result rows. The best solution is probably to put a LIMIT into the DECLARE CURSOR, so that the planner can see how much you intend to fetch. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Retry: Is this possible / slow performance?
>> The best solution is probably to put a LIMIT into the DECLARE CURSOR, >> so that the planner can see how much you intend to fetch. I assume that this limits the resultset to a LIMIT. That is not what I was hoping for. I was hoping for a way to scrolll throught the whole tables with orders. I have tested, and if one really wants the whole table the query with "set enable_seqscan = on" lasts 137 secs, the query with "set enable_seqscan = off" lasts 473 secs, so (alas), the planner is right. I sure would like to have ISAM like behaviour once in a while. Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Retry: Is this possible / slow performance?
> >> The best solution is probably to put a LIMIT into the DECLARE CURSOR, > >> so that the planner can see how much you intend to fetch. > I assume that this limits the resultset to a LIMIT. That is not what I was > hoping for. I was hoping for a way to scrolll throught the whole tables > with orders. > > I have tested, and if one really wants the whole table the query with "set > enable_seqscan = on" lasts 137 secs, the query with "set enable_seqscan = > off" lasts 473 secs, so (alas), the planner is right. > > I sure would like to have ISAM like behaviour once in a while. Then stop using cursors. A few months back I detailed the relative merits of using Cursors v. Queries to provide ISAM like functionality and Queries win hands down. Right now I am using pg as an ISAM backend for a relatively old and large COBOL ERP via a C++ ISAM driver, for which a publicly available version of the source will be available Real Soon Now :-). Merlin ---(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
[PERFORM] Solaris 9 tuning
Hi all, we have an Sun E3500 running Solaris 9. It's got 6x336MHz CPU and 10GB RAM. I would like to know what /etc/system and postgresql_conf values are recommended to deliver as much system resource as possible to Postgres. We use this Sun box solely for single user Postgres data warehousing workloads. Changes made to /etc/system values are: set shmsys:shminfo_shmmax=0x set shmsys:shminfo_shmmni=256 set shmsys:shminfo_shmseg=256 set shmsys:shminfo_shmmin=1 set semsys:seminfo_semmap=256 set semsys:seminfo_semmni=512 set semsys:seminfo_semmns=512 set semsys:seminfo_semmsl=32 Changes made to postgresql.conf are: shared_buffers = 50 sort_mem = 2097152 vacuum_mem = 100 Thanks. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Solaris 9 tuning
Paul, > I would like to know what /etc/system and postgresql_conf values are > recommended to deliver as much system resource as possible to Postgres. We > use this Sun box solely for single user Postgres data warehousing > workloads. What's your disk system? > shared_buffers = 50 This is highly unlikely to be optimal. That's 3GB. On test linux systems up to 8GB, we've not seen useful values of shared buffers anywhere above 400mb.How did you arrive at that figure? > sort_mem = 2097152 > vacuum_mem = 100 These could be fine on a single-user system. sort_mem is per *sort* though, not per query, so you'd need to watch out for complex queries spillling into swap; perhaps set it a 0.5GB or 1GB? Otherwise, start with the config guide at www.powerpostgresql.com/PerfList -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
