pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedQUERY PLAN:
4+ messages / 4 participants
[nested] [flat]
* QUERY PLAN:
@ 1999-10-27 10:03 Tim Joyce <tim@hoop.co.uk>
1999-10-27 15:09 ` Re: [GENERAL] QUERY PLAN: Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Tim Joyce @ 1999-10-27 10:03 UTC (permalink / raw)
To: pgsql-general
can someone point me at documentation so that i can unserstand the results
of an EXPLAIN, eg:
Index Scan using words_id_idx on books_idx (cost=441.19 rows=7644 width=8)
explain select * from books_idx where wrd_id=1;
I am paticularly interested in what the rows= figure means.
TIA
timj
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [GENERAL] QUERY PLAN:
1999-10-27 10:03 QUERY PLAN: Tim Joyce <tim@hoop.co.uk>
@ 1999-10-27 15:09 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
0 siblings, 0 replies; 4+ messages in thread
From: Ross J. Reedstrom @ 1999-10-27 15:09 UTC (permalink / raw)
To: Tim Joyce <tim@hoop.co.uk>; +Cc: pgsql-general
On Wed, Oct 27, 1999 at 11:03:45AM +0100, Tim Joyce wrote:
> can someone point me at documentation so that i can unserstand the results
> of an EXPLAIN, eg:
>
> Index Scan using words_id_idx on books_idx (cost=441.19 rows=7644 width=8)
>
> explain select * from books_idx where wrd_id=1;
>
> I am paticularly interested in what the rows= figure means.
I've picked up a little info on this from following the hackers list,
so I don't know what docs to point you at. My understanding is that the
rows= represents the number of tuples the optimizer estimates will be
returned by that step of the execution plan. In the example you've shown,
it's an index scan of a field, and the estimate (based on last know number
of tuples in the table (as of your last VACUUM ANALYZE) and an estimate
of the selectivity of the operator being applied to this index (<, =,
>, etc), and the approximate dispersion of the values in that field.
For more detail, I'd suggest checking the archives of the pgsql-hackers
list, and perhaps then asking on the list itself. There's some hints in the
"PostgreSQL Programmer's Guide" in the "Extending SQL" sections on functions,
operators, and interfacing them to indices.
Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
^ permalink raw reply [nested|flat] 4+ messages in thread
* query plan
@ 2023-11-17 20:00 Torsten Förtsch <tfoertsch123@gmail.com>
2023-11-17 20:14 ` Re: query plan Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Torsten Förtsch @ 2023-11-17 20:00 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
Hi,
This is part of a query plan:
Nested Loop Left Join (cost=26.32..47078866.36 rows=1344945195 width=626)
-> Nested Loop Left Join (cost=25.74..5312.48 rows=1344945195
width=608)
-> Nested Loop Left Join (cost=6.79..2876.77 rows=102 width=373)
-> Nested Loop Left Join (cost=1.90..1965.51 rows=102
width=361)
-> Bitmap Heap Scan on ... (cost=4.89..8.91 rows=2
width=28)
-> Hash Left Join (cost=18.95..42.61 rows=3 width=243)
-> Hash Left Join (cost=18.94..42.59 rows=3 width=203)
-> Hash (cost=0.00..0.00 rows=1 width=48)
-> Memoize (cost=0.58..4.59 rows=1 width=172)
What I don't understand is this. The left node of the join is expected to
return 102 rows. The right node 3. How can this result in >1e9 rows?
The query involved way further down a partitioned table with 2 partitions,
one pretty big in the 1e9 rows range, the other practically empty. The big
partition had been analyzed before. But the partitioned table and the empty
partition never. After analyzing them all was well.
I am just curious to understand how that number is calculated.
This is PG14.
Thanks.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: query plan
2023-11-17 20:00 query plan Torsten Förtsch <tfoertsch123@gmail.com>
@ 2023-11-17 20:14 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2023-11-17 20:14 UTC (permalink / raw)
To: Torsten Förtsch <tfoertsch123@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
=?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com> writes:
> This is part of a query plan:
> Nested Loop Left Join (cost=26.32..47078866.36 rows=1344945195 width=626)
> -> Nested Loop Left Join (cost=25.74..5312.48 rows=1344945195
> width=608)
> -> Nested Loop Left Join (cost=6.79..2876.77 rows=102 width=373)
> -> Nested Loop Left Join (cost=1.90..1965.51 rows=102
> width=361)
> -> Bitmap Heap Scan on ... (cost=4.89..8.91 rows=2
> width=28)
> -> Hash Left Join (cost=18.95..42.61 rows=3 width=243)
> -> Hash Left Join (cost=18.94..42.59 rows=3 width=203)
> -> Hash (cost=0.00..0.00 rows=1 width=48)
> -> Memoize (cost=0.58..4.59 rows=1 width=172)
> What I don't understand is this. The left node of the join is expected to
> return 102 rows. The right node 3. How can this result in >1e9 rows?
The rowcount estimate for the join is not derived by multiplying the
rowcount estimates of the two inputs. Having said that, this looks
pretty inconsistent. Can you show a test case that acts like that?
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-11-17 20:14 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-10-27 10:03 QUERY PLAN: Tim Joyce <tim@hoop.co.uk>
1999-10-27 15:09 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
2023-11-17 20:00 query plan Torsten Förtsch <tfoertsch123@gmail.com>
2023-11-17 20:14 ` Re: query plan Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox