Re: Takes too long to fetch the data from database - Mailing list pgsql-performance

I have flushed the database, so currently records in the "lan" table are: 665280

but records can be increased more than 1GB and in that case it takes more than 1 hour

Below is explain analyze output taken from the table having 665280 records

pdb=# explain analyze SELECT sdate, stime, rbts from lan WHERE (

 ( bname = 'pluto' ) AND ( cno = 17 ) AND ( pno = 1 ) AND ( ( sdate

 >= '2004-07-21' ) AND ( sdate <= '2004-07-21' ) )  )  ORDER BY sdate, stime ;

NOTICE:  QUERY PLAN:

Sort  (cost=17.13..17.13 rows=1 width=16) (actual time=619140.18..619140.29 rows

=288 loops=1)

  ->  Index Scan using lan_pkey on lan  (cost=0.00..17.12 rows=1 width=16) (ac

tual time=7564.44..619121.61 rows=288 loops=1)

Total runtime: 619140.76 msec

EXPLAIN

bsdb=# explain analyze SELECT DISTINCT sdate, stime, rbts from lan

 WHERE (  ( bname = 'pluto' ) AND ( cno = 17 ) AND ( pno = 1 ) AND ( ( sdate >= '2004-07-21' ) AND ( sdate <= '2004-07-21' ) )  ) 

 ORDER BY sdate, stime ;

NOTICE:  QUERY PLAN:

Unique  (cost=17.13..17.14 rows=1 width=16) (actual time=610546.66..610564.31 rows=288 loops=1)

  ->  Sort  (cost=17.13..17.13 rows=1 width=16) (actual time=610546.65..610546.75 rows=288 loops=1)

        ->  Index Scan using lan_pkey on lan  (cost=0.00..17.12 rows=1 width=16) (actual time=7524.47..610533.50 rows=288 loops=1)

Total runtime: 610565.51 msec

EXPLAIN

pdb=# explain analyze SELECT ALL sdate, stime, rbts from lan WHERE (  ( bname = 'neptune' ) AND ( cno = 17 ) AND ( pno = 1 ) AND ( ( sdate >= '2004-07-21' ) AND ( sdate <= '2004-07-21' ) )  )  ORDER BY sdate, stime ;

NOTICE:  QUERY PLAN:

Sort  (cost=17.13..17.13 rows=1 width=16) (actual time=1260756.66..1260756.76 rows=288 loops=1)

  ->  Index Scan using lan_pkey on lan  (cost=0.00..17.12 rows=1 width=16) (actual time=7725.97..1260752.47 rows=288 loops=1)

Total runtime: 1260757.09 msec

pdb=# \d lan

                     Table "lan"

      Column      |         Type          | Modifiers

------------------+-----------------------+-----------

 bname            | character varying(64) | not null

 sdate            | date                  | not null

 stime            | integer               | not null

 cno              | smallint              | not null

 pno              | smallint              | not null

 rbts             | bigint                |

 tbts             | bigint                |

 u_inpkt          | bigint                |

 u_outpkt         | bigint                |

 m_inpkt          | bigint                |

 m_outpkt         | bigint                |

 b_inpkt          | bigint                |

 b_outpkt         | bigint                |

Primary key: lan_pkey

Check constraints: "lan_stime" ((stime >= 0) AND (stime < 86400))