Re: SELECT (sometimes) returning Zero Rows? - Mailing list pgsql-general

It's version 7.0.3

Here's the output for the selects using explain below.
We had planned to do all of the optimization later. We just haven't had time
to get to it yet. Could that be the problem?

Anyhow here's the info. Please let me know if you need more information.

Appreciatively,
Matt Friedman.


parent_report_new=> explain
parent_report_new-> SELECT pr_article.title, pr_article.article_id FROM
pr_article WHERE
parent_report_new-> pr_article.article_id = pr_article_age.article_id AND
pr_article_age.age_id
parent_report_new-> = 1 AND pr_article.is_active = true AND
pr_article.is_approved = true AND
parent_report_new-> pr_article.release_date < 982093578 AND
((pr_article.end_date = 0) or
parent_report_new(> (pr_article.end_date > 982093578 )) ORDER BY
pr_article.release_date DESC
parent_report_new-> LIMIT 2;
NOTICE:  QUERY PLAN:

Sort  (cost=4.39..4.39 rows=1 width=24)
  ->  Nested Loop  (cost=0.00..4.38 rows=1 width=24)
        ->  Seq Scan on pr_article_age  (cost=0.00..1.68 rows=1 width=4)
        ->  Index Scan using pr_article_pkey on pr_article  (cost=0.00..2.03
rows=1 width=20)

EXPLAIN


parent_report_new=> explain
parent_report_new-> SELECT pr_article.title, pr_article.article_id FROM
pr_article WHERE
parent_report_new-> pr_article.article_id = pr_article_age.article_id AND
pr_article_age.age_id
parent_report_new-> = 1 AND pr_article.is_active = true AND
pr_article.is_approved = true AND
parent_report_new-> pr_article.release_date < 982093578 AND
((pr_article.end_date = 0) or
parent_report_new(> (pr_article.end_date > 982093578 )) ORDER BY
pr_article.release_date DESC
parent_report_new-> LIMIT 3;
NOTICE:  QUERY PLAN:

Sort  (cost=4.39..4.39 rows=1 width=24)
  ->  Nested Loop  (cost=0.00..4.38 rows=1 width=24)
        ->  Seq Scan on pr_article_age  (cost=0.00..1.68 rows=1 width=4)
        ->  Index Scan using pr_article_pkey on pr_article  (cost=0.00..2.03
rows=1 width=20)

EXPLAIN


parent_report_new=> explain
parent_report_new-> SELECT DISTINCT ON (release_date, article_id)
pr_article.title,
parent_report_new-> pr_article.article_id, pr_cat.prog_name FROM pr_article
WHERE
parent_report_new-> pr_article.article_id = pr_article_age.article_id AND
pr_article_age.age_id
parent_report_new-> = 1 AND pr_article_cat.cat_id=pr_cat.cat_id AND
pr_article.article_id =
parent_report_new-> pr_article_cat.article_id AND pr_article.is_active =
true AND
parent_report_new-> pr_article.is_approved = true AND
pr_article.release_date < 982093578 AND
parent_report_new-> ((pr_article.end_date = 0) or (pr_article.end_date >
982093578 )) ORDER BY
parent_report_new-> pr_article.release_date DESC LIMIT 5;
NOTICE:  QUERY PLAN:

Unique  (cost=7.63..7.64 rows=0 width=48)
  ->  Sort  (cost=7.63..7.63 rows=1 width=48)
        ->  Nested Loop  (cost=0.00..7.62 rows=1 width=48)
              ->  Nested Loop  (cost=0.00..5.58 rows=1 width=28)
                    ->  Nested Loop  (cost=0.00..4.36 rows=1 width=12)
                          ->  Seq Scan on pr_article_age  (cost=0.00..1.68
rows=1 width=4)
                          ->  Index Scan using pr_article_cat_article_id_key
on pr_article_cat  (cost=0.00..2.01 rows=1 width=8)
                    ->  Seq Scan on pr_cat  (cost=0.00..1.10 rows=10
width=16)
              ->  Index Scan using pr_article_pkey on pr_article
(cost=0.00..2.03 rows=1 width=20)

EXPLAIN


parent_report_new=> explain
parent_report_new-> SELECT DISTINCT ON (pr_cat.name) pr_cat.name,
pr_cat.prog_name FROM
parent_report_new-> pr_article, pr_article_age, pr_article_cat, pr_cat WHERE
parent_report_new-> pr_article_age.age_id = 1 AND pr_article_age.article_id
=
parent_report_new-> pr_article.article_id AND pr_article.article_id =
pr_article_cat.article_id
parent_report_new-> AND pr_article_cat.cat_id = pr_cat.cat_id AND
pr_article.is_active = true
parent_report_new-> AND pr_article.is_audio = false AND
pr_article.is_approved = true AND
parent_report_new-> pr_article.release_date < 982093578 AND
((pr_article.end_date = 0) or
parent_report_new(> (pr_article.end_date > 982093578 ))
parent_report_new-> ;
NOTICE:  QUERY PLAN:

Unique  (cost=7.64..7.64 rows=0 width=44)
  ->  Sort  (cost=7.64..7.64 rows=1 width=44)
        ->  Nested Loop  (cost=0.00..7.63 rows=1 width=44)
              ->  Nested Loop  (cost=0.00..6.40 rows=1 width=16)
                    ->  Nested Loop  (cost=0.00..4.36 rows=1 width=12)
                          ->  Seq Scan on pr_article_age  (cost=0.00..1.68
rows=1 width=4)
                          ->  Index Scan using pr_article_cat_article_id_key
on pr_article_cat  (cost=0.00..2.01 rows=1 width=8)
                    ->  Index Scan using pr_article_pkey on pr_article
(cost=0.00..2.03 rows=1 width=4)
              ->  Seq Scan on pr_cat  (cost=0.00..1.10 rows=10 width=28)

EXPLAIN





----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Matt Friedman" <matt@sprynewmedia.com>
Cc: "PgSql General List" <pgsql-general@postgresql.org>
Sent: Tuesday, February 13, 2001 3:46 PM
Subject: Re: SELECT (sometimes) returning Zero Rows?


> "Matt Friedman" <matt@daart.ca> writes:
> > We are having what seems like a baffling problem to me.
> > On occasion, our SELECTs will return zero rows when we know with
certainty
> > that they should be returning at least some rows. No error occurs, the
> > select just behaves as if the db is empty or something.
>
> What PG version is this?  What does EXPLAIN show for the misbehaving
> queries?
>
> regards, tom lane
>
>