[PERFORM] Indexscan is only used if we use "limit n"
Hello, Whe are running PostgreSQL 8.2.0 on amd64-portbld-freebsd6.2, compiled by GCC cc (GCC) 3.4.6 [FreeBSD] 20060305. The query only uses the index if we have a "limit n": Without "Limit n" explain select esapcuit, esapcuil from esact00 t1 order by esapcuit, esapcuil Sort (cost=843833.82..853396.76 rows=3825177 width=30) Sort Key: esapcuit, esapcuil -> Seq Scan on esact00 t1 (cost=0.00..111813.77 rows=3825177 width=30) With "Limit n" explain select esapcuit, esapcuil from esact00 t1 order by esapcuit, esapcuil limit 1 Limit (cost=0.00..1.86 rows=1 width=30) -> Index Scan using uesact002 on esact00 t1 (cost=0.00..7129736.89 rows=3825177 width=30) Our postgresql.conf is: enable_bitmapscan = on enable_hashagg = on enable_hashjoin = on enable_indexscan = on enable_mergejoin = on enable_nestloop = on enable_seqscan = on enable_sort = on enable_tidscan = on Thank you. Sebastián Sebastián Baioni http://www.acomplejados.com.ar http://www.extremista.com.ar http://www.coolartists.com.ar - ¡Sé un mejor ambientalista! Encontrá consejos para cuidar el lugar donde vivimos..
Re: [PERFORM] Indexscan is only used if we use "limit n"
Sebastián Baioni escribió: > Hello, > Whe are running PostgreSQL 8.2.0 on amd64-portbld-freebsd6.2, compiled by GCC > cc (GCC) 3.4.6 [FreeBSD] 20060305. > The query only uses the index if we have a "limit n": > Without "Limit n" > explain > select esapcuit, esapcuil > from esact00 t1 > order by esapcuit, esapcuil > > Sort (cost=843833.82..853396.76 rows=3825177 width=30) > Sort Key: esapcuit, esapcuil > -> Seq Scan on esact00 t1 (cost=0.00..111813.77 rows=3825177 width=30) That's right. What else did you expect? It estimates it has to return 3 million rows after all -- using an indexscan would be slow. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J "Puedes vivir solo una vez, pero si lo haces bien, una vez es suficiente" ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Indexscan is only used if we use "limit n"
which column does your indice cover? Em Qua, 2007-08-15 às 16:36 -0300, Sebastián Baioni escreveu: > Hello, > Whe are running PostgreSQL 8.2.0 on amd64-portbld-freebsd6.2, compiled > by GCC cc (GCC) 3.4.6 [FreeBSD] 20060305. > The query only uses the index if we have a "limit n": > > Without "Limit n" > explain > select esapcuit, esapcuil > from esact00 t1 > order by esapcuit, esapcuil > > Sort (cost=843833.82..853396.76 rows=3825177 width=30) > Sort Key: esapcuit, esapcuil > -> Seq Scan on esact00 t1 (cost=0.00..111813.77 rows=3825177 > width=30) > > With "Limit n" > explain > select esapcuit, esapcuil > from esact00 t1 > order by esapcuit, esapcuil > limit 1 > > Limit (cost=0.00..1.86 rows=1 width=30) > -> Index Scan using uesact002 on esact00 t1 (cost=0.00..7129736.89 > rows=3825177 width=30) > > Our postgresql.conf is: > enable_bitmapscan = on > enable_hashagg = on > enable_hashjoin = on > enable_indexscan = on > enable_mergejoin = on > enable_nestloop = on > enable_seqscan = on > enable_sort = on > enable_tidscan = on > > Thank you. > Sebastián > > > Sebastián Baioni > http://www.acomplejados.com.ar > http://www.extremista.com.ar > http://www.coolartists.com.ar > > > __ > > ¡Sé un mejor ambientalista! > Encontrá consejos para cuidar el lugar donde vivimos.. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Indexscan is only used if we use "limit n"
On Wed, 2007-08-15 at 16:36 -0300, Sebastián Baioni wrote: > Hello, > Whe are running PostgreSQL 8.2.0 on amd64-portbld-freebsd6.2, compiled > by GCC cc (GCC) 3.4.6 [FreeBSD] 20060305. > The query only uses the index if we have a "limit n": > > Without "Limit n" > explain > select esapcuit, esapcuil > from esact00 t1 > order by esapcuit, esapcuil > > Sort (cost=843833.82..853396.76 rows=3825177 width=30) > Sort Key: esapcuit, esapcuil > -> Seq Scan on esact00 t1 (cost=0.00..111813.77 rows=3825177 > width=30) > > With "Limit n" > explain > select esapcuit, esapcuil > from esact00 t1 > order by esapcuit, esapcuil > limit 1 This isn't really unexpected-- it's faster to do a full sequential scan of a table than it is to do a full index traversal over the table. And usually it's still cheaper even after sorting the results of the full table scan. So as near as we can tell, PG is just doing what it's supposed to do and picking the best plan it can. You didn't really ask a question-- is this causing problems somehow, or were you just confused by the behavior? -- Mark ---(end of broadcast)--- TIP 1: 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] Stable function optimisation
Hi Tom, Thank you very much for your explanation. On 13.08.2007, at 23:01, Tom Lane wrote: Philipp Specht <[EMAIL PROTECTED]> writes: The biggest question here is: Why is the runtime of the query with the stable function not near the runtime of the immutable function? Stable functions don't get folded to constants. I tried to force this by using the following construct: SELECT t.id, t.a FROM public.t WHERE t.a=(VALUES(public.f())); Is this a bad practice and will destroy some other thing I can't think of at the moment? What it means for me at the moment is about half the query time of a high usage query directly linked to a gui. That's a big gain for a user interface and takes the query under the magical 500ms response time... It's definitely one query and the manual states that a stable function does not change in one statement and therefore can be optimised. That's not the type of optimization that gets done with it. What "STABLE" is for is marking functions that are safe to use in index conditions. If you'd been using an indexable condition you'd have seen three different behaviors here. (I see that you do have an index on t.a, but apparently there are too many matching rows for the planner to think the index is worth using.) Yes, that's not the real problem here. It's only a test database and the real data behaves a bit differently. Have a nice day, Philipp ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
